Count() multiple columns

hi

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;

But this just returns same count for both prereq_count and step_count

I also tried to look at

SELECT prereq_count=(SELECT COUNT(*) FROM mods_prereqs),
step_count=(SELECT COUNT(*) FROM mods_slide) ....

MySQL doesn't support this 2nd style?

Is there any way to get two column counts and a set of data in one
SELECT under MySQL?

Thanks in advance for any help.

Re: Count() multiple columns

On 15 May, 09:35, Tyno Gendo wrote:
> hi
>
> 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;
>
> But this just returns same count for both prereq_count and step_count
>
> I also tried to look at
>
> SELECT prereq_count=(SELECT COUNT(*) FROM mods_prereqs),
> step_count=(SELECT COUNT(*) FROM mods_slide) ....
>
> MySQL doesn't support this 2nd style?
>
> Is there any way to get two column counts and a set of data in one
> SELECT under MySQL?
>
> Thanks in advance for any help.

Depending on your version of MySQL you can use subselects, just not in
that syntax.

Re: Count() multiple columns

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.

Re: Count() multiple columns

On 15 May, 10:31, Boris Stumm 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))

Re: Count() multiple columns

Captain Paralytic wrote:
> On 15 May, 10:31, Boris Stumm wrote:
>> 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))

Yes, if the DBMS supports that :-)
(I usually dont use MySQL)

Re: Count() multiple columns

Hi Tyno
you could try

SELECT
COUNT(modpr_prereq or null) as prereq_count,
COUNT(mods_slide or null) 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;

Frankie
http://puity.com


"Tyno Gendo" a écrit dans le message de news:
SIednU9JC4YG7dTbRVnytgA@bt.com...
> hi
>
> 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;
>
> But this just returns same count for both prereq_count and step_count
>
> I also tried to look at
>
> SELECT prereq_count=(SELECT COUNT(*) FROM mods_prereqs),
> step_count=(SELECT COUNT(*) FROM mods_slide) ....
>
> MySQL doesn't support this 2nd style?
>
> Is there any way to get two column counts and a set of data in one SELECT
> under MySQL?
>
> Thanks in advance for any help.