From: Axel Rau <Axel.Rau@Chaos1.DE>
To: pgsql-sql@postgresql.org
Subject: Selecting count of details along with details columns
Date: Wed, 28 Sep 2005 21:19:25 +0200
Message-ID: <8365994213849c93946e61ded7d12318@Chaos1.DE> (raw)
Dear pgsql-admin members:
Having 2 tables:
CREATE TABLE T2 (
id serial PRIMARY KEY,
T2_name text
);
CREATE TABLE T1 (
id serial PRIMARY KEY,
T1_name text,
fk_t2 int4 REFERENCES t2
);
And some rows:
INSERT INTO T2 (T2_name) VALUES('T2-N1');
INSERT INTO T2 (T2_name) VALUES('T2-N2');
INSERT INTO T2 (T2_name) VALUES('T2-N3');
INSERT INTO T2 (T2_name) VALUES('T2-N4');
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-AAA', 1);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-BBB', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-CCC', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-DDD', 2);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-EEE', 3);
INSERT INTO T1 (T1_name, fk_t2) VALUES('T1-FFF', 3);
It is possible to show how many details exist and to limit result with
HAVING:
SELECT T2.T2_name, COUNT(T1.id) AS xx
FROM T2, T1
WHERE T2.id = T1.fk_t2
GROUP BY T2.T2_name
HAVING COUNT(T1.id) > 1
ORDER BY xx DESC;
t2_name | xx
---------+----
T2-N2 | 3
T2-N3 | 2
(2 rows)
Adding column t1_name to the result set breaks COUNT(T1.id):
SELECT T2.T2_name, T1.T1_name, COUNT(T1.id) AS xx
FROM T2, T1
WHERE T2.id = T1.fk_t2
GROUP BY T2.T2_name, T1.T1_name
HAVING COUNT(T1.id) > 1
ORDER BY xx DESC;
t2_name | t1_name | xx
---------+---------+----
(0 rows)
How can I do this with pg ?
Looking for a workaround, I learned that aggregate functions are not
allowed in WHERE clauses.
Question: Is this conform with the standard?
Sorry, if this has been discussed earlier.
Axel
Axel Rau, Frankfurt, Germany +49-69-951418-0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: Axel.Rau@Chaos1.DE
Subject: Re: Selecting count of details along with details columns
In-Reply-To: <8365994213849c93946e61ded7d12318@Chaos1.DE>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox