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 04675D9BD6 for ; Thu, 29 Sep 2005 06:19:52 -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 33954-09 for ; Thu, 29 Sep 2005 09:19:44 +0000 (GMT) Received: from mail1.chaos1.de (Mail1.Chaos1.DE [213.160.12.53]) by svr1.postgresql.org (Postfix) with ESMTP id 47C0DD6ECD for ; Thu, 29 Sep 2005 06:19:44 -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 1EKuZt-0000kN-64; Thu, 29 Sep 2005 11:19:45 +0200 In-Reply-To: <433BA637.7040106@archonet.com> References: <8365994213849c93946e61ded7d12318@Chaos1.DE> <433BA637.7040106@archonet.com> Mime-Version: 1.0 (Apple Message framework v622) Content-Type: multipart/signed; protocol="application/pgp-signature"; micalg=pgp-sha1; boundary="Apple-Mail-25--645476046" Message-Id: Cc: pgsql-sql@postgresql.org From: Axel Rau Subject: Re: Selecting count of details along with details columns Date: Thu, 29 Sep 2005 11:19:39 +0200 To: Richard Huxton Content-Transfer-Encoding: 7bit X-Pgp-Agent: GPGMail 1.0.2 X-Mailer: Apple Mail (2.622) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.145 required=5 tests=[AWL=-0.062, UPPERCASE_25_50=0.207] X-Spam-Level: X-Archive-Number: 200509/294 X-Sequence-Number: 22841 --Apple-Mail-25--645476046 Content-Type: multipart/alternative; boundary=Apple-Mail-24--645476052 --Apple-Mail-24--645476052 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII; format=flowed Am 29.09.2005 um 10:30 schrieb Richard Huxton: > Axel Rau wrote: >> 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 ? > > Do what? You don't say what results you are expecting. > > Do you want: > 1. ALL values of T1_name (in which case what count do you want)? > 2. The FIRST value of T1_name (in which case what do you mean by > first)? #1.: t2_name | t1_name | count ---------+---------+------- T2-N2 | T1-CCC | 3 T2-N3 | T1-FFF | 2 T2-N2 | T1-BBB | 3 T2-N2 | T1-DDD | 3 T2-N3 | T1-EEE | 2 (5 rows) Sorry, for not making this clear. Thank you for taking the time, Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 --Apple-Mail-24--645476052 Content-Transfer-Encoding: 7bit Content-Type: text/enriched; charset=US-ASCII Am 29.09.2005 um 10:30 schrieb Richard Huxton: Axel Rau wrote: 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 ? Do what? You don't say what results you are expecting. Do you want: 1. ALL values of T1_name (in which case what count do you want)? 2. The FIRST value of T1_name (in which case what do you mean by first)? #1.: t2_name | t1_name | count ---------+---------+------- T2-N2 | T1-CCC | 3 T2-N3 | T1-FFF | 2 T2-N2 | T1-BBB | 3 T2-N2 | T1-DDD | 3 T2-N3 | T1-EEE | 2 (5 rows) Sorry, for not making this clear. Thank you for taking the time, Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 --Apple-Mail-24--645476052-- --Apple-Mail-25--645476046 content-type: application/pgp-signature; x-mac-type=70674453; name=PGP.sig content-description: Signierter Teil der Nachricht content-disposition: inline; filename=PGP.sig content-transfer-encoding: 7bit -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Darwin) iQEVAwUBQzuxsMFz9+6bacTRAQKy/QgAovQyDo7X6g5HVSkzVIhE63A2WOb5+O7+ 0uLFEMcBWWXUSqsYStXyyd0cbSnkhbszWDC7gA2O4kJFb4WCBZS727wk0hss/Yqi 4ktlH+7YYEMVBDmv1pjIBH+u+OEdM1FMIznHc7p9v9rjlQK/6bQubmKWlwnSoD+a aJaprTuvuroONiPbpCRm9Eb7+OAVGKaYX/R5FJhj66Hh8L8+IHUubKr5MTN1Mz35 UMPVCi0rncEAo1yR+Ar1TgZ2jF9/cFvZOXyNhttRWm/DlW6FksQ8KX9bnHYqc7TV 8bjTiuN93SUMW/LZa/S+AyADfKffqV3rHc6NLa/XKKo1V035OL16zQ== =a+4k -----END PGP SIGNATURE----- --Apple-Mail-25--645476046--