[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)

  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

This email proudly and graciously contributes to entropy.

More information about the thelist mailing list