Selecting count of details along with details columns - Mailing list 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