pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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






view thread (12+ messages)  latest in thread

Message-ID: <Pine.GSO.4.31.0101040542540.4164-100000@sysadmin>
Permalink:  ../Pine.GSO.4.31.0101040542540.4164-100000@sysadmin/
Also on:    postgresql.org/message-id/Pine.GSO.4.31.0101040542540.4164-100000@sysadmin

 · 

reply

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