[thelist] MySQL multiple counts problem / help needed
Ian Goodyer
ig at meeting-place.co.uk
Tue Oct 25 04:28:49 CDT 2005
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
More information about the thelist
mailing list