X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by svr1.postgresql.org (Postfix) with ESMTP id 392E9D9AA7
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 28 Sep 2005 16:19:31 -0300 (ADT)
Received: from svr1.postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 81365-03
	for <pgsql-sql-postgresql.org@localhost.postgresql.org>;
	Wed, 28 Sep 2005 19:19:27 +0000 (GMT)
Received: from mail1.chaos1.de (Mail1.Chaos1.DE [213.160.12.53])
	by svr1.postgresql.org (Postfix) with ESMTP id 231F8D9A91
	for <pgsql-sql@postgresql.org>; Wed, 28 Sep 2005 16:19:26 -0300 (ADT)
Received: from claudius6.in.chaos1.de ([192.168.220.106])
	by mail1.chaos1.de with esmtpsa (TLSv1:RC4-SHA:128) (Exim 4.44)
	id 1EKhSg-00021P-5x
	for pgsql-sql@postgresql.org; Wed, 28 Sep 2005 21:19:26 +0200
Mime-Version: 1.0 (Apple Message framework v622)
To: pgsql-sql@postgresql.org
Message-Id: <8365994213849c93946e61ded7d12318@Chaos1.DE>
Content-Type: multipart/alternative; boundary=Apple-Mail-17--695889613
From: Axel Rau <Axel.Rau@Chaos1.DE>
Subject: Selecting count of details along with details columns
Date: Wed, 28 Sep 2005 21:19:25 +0200
X-Mailer: Apple Mail (2.622)
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, hits=0.139 required=5 tests=[AWL=-0.068,
	UPPERCASE_25_50=0.207]
X-Spam-Level: 
X-Archive-Number: 200509/283
X-Sequence-Number: 22830


--Apple-Mail-17--695889613
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
	charset=US-ASCII;
	format=flowed

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
--Apple-Mail-17--695889613
Content-Transfer-Encoding: 7bit
Content-Type: text/enriched;
	charset=US-ASCII

Dear pgsql-admin members:


Having 2 tables:


<fontfamily><param>Courier</param>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 

);


</fontfamily>And some rows:


<fontfamily><param>Courier</param>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);

</fontfamily>

It is possible to show how many details exist and to limit result with
HAVING:


<fontfamily><param>Courier</param>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;

</fontfamily>

<fontfamily><param>Courier</param> t2_name | xx 

---------+----

 T2-N2   |  3

 T2-N3   |  2</fontfamily>

<fontfamily><param>Courier</param>(2 rows)

</fontfamily>

Adding column t1_name to the result set breaks
<fontfamily><param>Courier</param>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;

</fontfamily>

<fontfamily><param>Courier</param> t2_name | t1_name | xx 

---------+---------+----

(0 rows)

</fontfamily>

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
--Apple-Mail-17--695889613--