[thelist] MySQL multiple counts problem / help needed

[Matt Warden]

On 10/24/05, Ian Goodyer <ig at meeting-place.co.uk> wrote:
> I would like to set up a MYSQL query to display the number of guests
> (sub=0), full members (sub=1) and hidden members (sub=2) for each site.
>
> I can show the number of guests like this of course:
>
> SELECT site, sub, COUNT( * ) AS guests
> FROM users
> WHERE sub = 0
> GROUP BY site

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


lemme know how it works,

--
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.