[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