Posted by Captain Paralytic on 05/15/07 10:00

On 15 May, 10:31, Boris Stumm <s...@informatik.uni-kl.de> wrote:
> Tyno Gendo wrote:
> > i'm just wondering, is it possible to count the number of rows without
> > NULL/blanks in for two columns in one select?
>
> > I tried: SELECT module.*, COUNT( modpr_prereq ) AS prereq_count, COUNT(
> > mods_slide ) AS step_count FROM module LEFT JOIN mod_prereqs ON
> > mod_prereqs.modpr_mod = module.mod_seq LEFT JOIN mod_slide ON
> > mod_slide.mods_mod_seq = module.mod_seq GROUP BY mod_seq;
>
> Instead of count(col) you can do a
> sum(case when col is null then 0 else 1 end)
>
> I am not sure if that is the most clever solution, and if it works with
> MySQL, but for me it worked when I wanted two counts in one select.

Wouldn't that be slightly neater as
SUM(IF(`col`,1,0))

[Back to original message]