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 BED4CD9B7D for ; Thu, 29 Sep 2005 16:08:22 -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 22006-06 for ; Thu, 29 Sep 2005 19:08:18 +0000 (GMT) Received: from mail1.chaos1.de (Mail1.Chaos1.DE [213.160.12.53]) by svr1.postgresql.org (Postfix) with ESMTP id 46CEFD81CE for ; Thu, 29 Sep 2005 16:08:17 -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 1EL3lS-0001ha-5n; Thu, 29 Sep 2005 21:08:18 +0200 In-Reply-To: <433BBC07.5040208@archonet.com> References: <8365994213849c93946e61ded7d12318@Chaos1.DE> <433BA637.7040106@archonet.com> <433BBC07.5040208@archonet.com> Mime-Version: 1.0 (Apple Message framework v622) Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: <409f1e679d5f8744e1609d5c447d5f15@Chaos1.DE> Content-Transfer-Encoding: 7bit Cc: pgsql-sql@postgresql.org From: Axel Rau Subject: Re: Selecting count of details along with details columns Date: Thu, 29 Sep 2005 21:08:15 +0200 To: Richard Huxton 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.046 required=5 tests=[AWL=0.046] X-Spam-Level: X-Archive-Number: 200509/297 X-Sequence-Number: 22844 -----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Am 29.09.2005 um 12:03 schrieb Richard Huxton: > Axel Rau wrote: >> Am 29.09.2005 um 10:30 schrieb Richard Huxton: >>> Axel Rau wrote: >>> >>>> ... > Ah - this is two questions: > 1. What are the unique (t2_name,t1_name) pairings? > 2. How many different (t1.id) values are there for each t2. > > So - something like: > > SELECT names.T2_name, names.T1_name, counts.num_t2 > FROM > ( > SELECT DISTINCT T2.T2_name, T1.T1_name > FROM T2,T1 > WHERE T2.id = T1.fk_t2 > ) AS names, > ( > SELECT T2.T2_name, COUNT(T1.id) AS num_t2 > FROM T2, T1 > WHERE T2.id = T1.fk_t2 > GROUP BY T2.T2_name > HAVING COUNT(T1.id) > 1 > ) AS counts > WHERE > names.T2_name = counts.T2_name > ; > > You could write the "names" sub-query with a GROUP BY if you wanted of > course. Exactly, that query works as I expected. Thank you. Can you answer this question as well: > Looking for a workaround, I learned that aggregate functions are not > allowed in WHERE clauses. > Question: Is this conform with the standard? Axel Axel Rau, Frankfurt, Germany +49-69-951418-0 -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Darwin) iQEVAwUBQzw7n8Fz9+6bacTRAQIqnAf9EW7TS7K+cCf95fosagOcNhgQFuUvlyUr yJpkXrv83+oKJ6kw6OcJxaEAkuiyRIiGQAlsVfc86itgKUQLfq6qpXEjeMD459kb wIO01LV37akn9y3420h4Pmi1SDaZ63oUWJn48DhlUuuh5B7LHNyiOSMUKLU8ptLd ZQ875uPo235bdqb15ibmZtwAuMGdsf3PPySBYMzvHzk7uZ+68b50QTmTPSU7VuPd XtbZWdTK8q6+R3mhgz6k7DFaqTlTqzMimQevmwb1ADZZGVOOC0i77M1axYsCHarB i2RT1CAcnNCX8MYc2nt8HS4j5KXpq7POFk3vdyAmVMwZ8WHNWJP2/w== =CJSg -----END PGP SIGNATURE-----