Re: Query query

On Wed, 2001-11-28 at 08:00, Andrew Bell wrote:
>
> Given the following table and the constraint cat2 = 1
>
> cat1 | cat2
>
> A 1
> B 1
> A 2
> B 2
> B 3
> B 3
>
> I want to generate output that looks like:
>
> cat1 | count | count
> A 1 1
> B 1 3
>
> Where the first 'count' represents the number of things that match the
> constraint, and the second 'count' represents the number of things that
> doesn't match the constraint.
>
> Can this be done? If so how?

test=# select distinct cat1, (select count(*) from t t_1 where t_1.cat1
= t.cat1 AND t_1.cat2 = 1) as count, (select count(*) from t t_2 where
t_2.cat1 = t.cat1 and t_2.cat2 != 1) as count from t;
cat1 | count | count
------+-------+-------
A | 1 | 1
B | 1 | 3
(2 rows)

test=# select version();
version
---------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.4
(1 row)

But I would keep a weather eye on the costs for this sort of query -
just because it can be done in SQL like this doesn't necessarily mean it
_should_ be done!.

Triggers maintaining summary data on a joined table may be a more
efficient approach, depending on data volumes and rates of inserts /
queries.

test=# explain select distinct cat1, (select count(*) from t t_1 where
t_1.cat1 = t.cat1 AND t_1.cat2 = 1) as count, (select count(*) from t
t_2 where t_2.cat1 = t.cat1 and t_2.cat2 != 1) as count from t;
NOTICE: QUERY PLAN:

Unique (cost=1.14..1.18 rows=1 width=12)
-> Sort (cost=1.14..1.14 rows=6 width=12)
-> Seq Scan on t (cost=0.00..1.06 rows=6 width=12)
SubPlan
-> Aggregate (cost=1.09..1.09 rows=1 width=0)
-> Seq Scan on t t_1 (cost=0.00..1.09 rows=1
width=0)
-> Aggregate (cost=1.09..1.09 rows=1 width=0)
-> Seq Scan on t t_2 (cost=0.00..1.09 rows=1
width=0)

EXPLAIN

Regards,
Andrew.
--
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201 MOB: +64(21)635-694 OFFICE: +64(4)499-2267