[thelist] MySQL multiple counts problem / help needed

[Ian Goodyer]

Matt Warden <mwarden at gmail.com> wrote:

>maybe something like...
>select site, sum(case sub when 0 then 1 else 0) as sub0cnt, sum(case sub
when 1 then 1 else 0)
>as sub1cnt, sum(case sub when 2 then 1 else 0) as sub2cnt from users group
by site

Thanks Matt for your help.  That brought me into areas of SQL that I did not
know existed, the CASE statement.  Your query nearly worked but I needed to
END each case statement.  The final query was:


select site, sum(case sub when 0 then 1 else 0 end) as sub0cnt, sum(case sub
when 1 then 1 else 0 end)
as sub1cnt, sum(case sub when 2 then 1 else 0 end) as sub2cnt from users
group by site

That worked perfectly!!

Thanks again.

Ian