Received: from sysadmin.chi.wdr.com (sysadmin.chi.ubswarburg.com [146.180.1.66]) by mail.postgresql.org (8.11.1/8.11.1) with ESMTP id f04Bt9785365 for ; Thu, 4 Jan 2001 06:55:10 -0500 (EST) (envelope-from devin@sysadmin.chi.wdr.com) Received: from devin by sysadmin.chi.wdr.com with local (Exim 3.12 #2) id 14E8zD-00015T-00; Thu, 04 Jan 2001 05:55:15 -0600 Date: Thu, 4 Jan 2001 05:55:15 -0600 (CST) From: Tod McQuillin X-X-Sender: To: Jens Hartwig cc: Tom Lane , Nikolaj Lundsgaard , Subject: Re: Sv: how to build this query ??? Please help !!! In-Reply-To: <3A545AF2.1C827233@debis.com> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200101/51 X-Sequence-Number: 712 On Thu, 4 Jan 2001, Jens Hartwig wrote: > This would be a self-join of one table like: > > select ord.a_nr, > c1.count(*), > c2.count(*), > ... > from orders ord, > cylinders c1, > cylinders c2, > ... > where c1.z_a_nr = ord.a_nr > and c2.z_status = 'zdr' > and ... > > This in fact is not possible in PostgreSQL (it seems that the table > alias "c1" cannot be prefixed to the aggregate-function "count(*)") That's true, and you can't say count(c1.*) either, but you *can* say count(c1.z_status)... Continuing from your example using tables x and y: # select count(x1.a) as ones, count(x2.a) as twos from x x1, x x2 where x1.a = 1 and x2.a = 2; ones | twos ------+------ 1 | 1 (1 row) But it doesn't do what you want: # insert into x(a) values(2); INSERT 313887 1 # select count(x1.a) as ones, count(x2.a) as twos from x x1, x x2 where x1.a = 1 and x2.a = 2; ones | twos ------+------ 2 | 2 (1 row) An inspection of the cartesian product (select x1.a, x2.a from x x1, x x2) will make it clear why it doesn't work. I can't think of any way to get this: ones | twos ------+------ 1 | 2 without subqueries like so: # select (select count(*) from x where a = 1) as ones, (select count(*) from x where a = 2) as twos; But, to answer your question, "Does this at all correlate with the philosophy of a relational database?" ... My answer is yes! After all, isn't it just the same as "select a, count(a) from x group by a" turned sideways? If you can think of how to do this "the hard way" (i.e. without subselects or temp tables etc.) please share. -- Tod McQuillin