Skip site navigation (1) Skip section navigation (2)

Re: Query query

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 (view raw or flat)
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


In response to

pgsql-novice by date

Next:From: Tom LaneDate: 2001-11-28 15:11:05
Subject: Re: [HACKERS] upper and lower doesn't work with german
Previous:From: Vincent.GaboriauDate: 2001-11-28 08:37:18
Subject: Re: [HACKERS] upper and lower doesn't work with german

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group