Thread: Query query

Hi,

You have all been so helpful, I'm going to bother you with another.  Say
that you have a table with two categories.  I want to do a *single* query
that shows the number of things in each of the primary category that match
a constraint and
the number of things that don't match that constraint.

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?

Thanks,


-- Andrew Bell
acbell@iastate.edu

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