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
