[thelist] count() multiple columns in a single query?

Matt Warden mwarden at gmail.com
Thu Dec 13 12:47:20 CST 2007


On 12/13/07, Jeremy Weiss <eccentric.one at gmail.com> wrote:
> Script 1 - looks for records with nothing in the closed column
> Select COUNT(*) FROM followup WHERE fk_id=$id AND ((closed IS NULL) OR
> (closed = ''))
>
> Script 2 - looks for records from the same records set as above for records
> with nothing in the received column
> Select COUNT(*) FROM followup WHERE fk_id=$id AND received_by != '' AND
> ((closed IS NULL) OR (closed = ''))

Try this:

select
     sum(case when fk_id=$id and (closed is null or closed='') and
received_by='' then 1 else 0) as first_count,
     sum(case when fk_id=$id and (closed is null or closed='') and
received_by!='' then 1 else 0) as second_count
from followup

So, you can see I basically took your where clauses and made them case
conditions. The following reformulation is equivalent, since much of
the where clauses are the same:

select
     sum(case when received_by='' then 1 else 0) as first_count,
     sum(case when received_by!='' then 1 else 0) as second_count
from followup
where  fk_id=$id and (closed is null or closed='')


-- 
Matt Warden
Cincinnati, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.



More information about the thelist mailing list