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