From: | Axel Rau <Axel(dot)Rau(at)Chaos1(dot)DE> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Selecting count of details along with details columns |
Date: | 2005-09-28 19:19:25 |
Message-ID: | 8365994213849c93946e61ded7d12318@Chaos1.DE |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Daryl Richter | 2005-09-28 19:38:05 | Re: changing a column's position in table, how do you do that |
Previous Message | Tom Lane | 2005-09-28 17:52:48 | Re: Problem with function and trigger... |