[SQL] Selecting count of details along with details columns

[Axel Rau]

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