From: | Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz> |
---|---|
To: | Andrew Bell <acbell(at)iastate(dot)edu> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Query query |
Date: | 2001-11-28 11:11:54 |
Message-ID: | 1006945914.1191.6.camel@kant.mcmillan.net.nz |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-11-28 15:11:05 | Re: [HACKERS] upper and lower doesn't work with german |
Previous Message | Vincent.Gaboriau | 2001-11-28 08:37:18 | Re: [HACKERS] upper and lower doesn't work with german |