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