From: Tod McQuillin <devin@spamcop.net>
To: Jens Hartwig <jhartwig@debis.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Nikolaj Lundsgaard <Nikolaj@konfus.dk>
Cc: pgsql-sql@postgresql.org
Subject: Re: Sv: how to build this query ??? Please help !!!
Date: Thu, 4 Jan 2001 05:55:15 -0600 (CST)
Message-ID: <Pine.GSO.4.31.0101040542540.4164-100000@sysadmin> (raw)
In-Reply-To: <3A545AF2.1C827233@debis.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: devin@spamcop.net, jhartwig@debis.com, tgl@sss.pgh.pa.us, Nikolaj@konfus.dk
Subject: Re: how to build this query ??? Please help !!!
In-Reply-To: <Pine.GSO.4.31.0101040542540.4164-100000@sysadmin>
References: <3A545AF2.1C827233@debis.com> <Pine.GSO.4.31.0101040542540.4164-100000@sysadmin>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox