[thelist] MySQL multiple counts problem

Matt Warden mwarden at gmail.com
Fri Feb 13 07:24:53 CST 2009


On Fri, Feb 13, 2009 at 1:14 AM, Cristian Rosescu <crosescu at gmail.com> wrote:
> I would like to setup one MySql query that will display this number of
> motels have breakfast available (breakfast_available = 1) and this number of
> motels provide conference facilities (conference_facilities = 1)

select
  sum( case when breakfast_available=1 then 1 else 0 end ) as motels_breakfast
  sum( case when conference_facilities=1 then 1 else 0 end ) as motels_conf
  sum( case when breakfast_available=1
             and conference_facilities=1 then 1 else 0 ) as motels_both
  .. etc..
from ...

If your fields are really 0 and 1, then you can skip some of the case
statements and just sum the fields, but I think that's less readable
and you'll have to do the case statement for complex conditions like
the last example above.

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


This email proudly and graciously contributes to entropy.



More information about the thelist mailing list