Re: Sv: how to build this query ??? Please help !!!

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