[thelist] count() multiple columns in a single query?

[Matt Warden]

On 12/13/07, Jeremy Weiss <eccentric.one at gmail.com> wrote:
> Matt, thanks for getting back with me.
>
> Both of the queries threw errors though, shown below, that don't make a lick
> of sense to me.
>
> You have an error in your SQL syntax; check the manual that corresponds to
> your MySQL server version for the right syntax to use near 'and (closed is
> null or closed='') and received_by = '' then 1 else 0) as first_c' at line 1
>
> You have an error in your SQL syntax; check the manual that corresponds to
> your MySQL server version for the right syntax to use near ') as
> first_count, sum(case when received_by!='' then 1 else 0) as second_count f'
> at line 1
>
> For what it's worth, this is on a MySQL 5 server.

Yeah, I am a dope and forgot the 'end' keyword. I do this frequently
and I'm seeking therapy.

select
    sum(case when received_by='' then 1 else 0 end) as first_count,
    sum(case when received_by!='' then 1 else 0 end) as second_count
from followup
where  fk_id=$id and (closed is null or closed='')

As for how to make sense of this, basically the case statement is
adding a 1 for each row where received_by='' (or received_by!='' in
the second case) and adding a 0 otherwise. This effectively gives you
the count you want. Note taht all rows are filtered by the common
criteria in the where clause, so those criteria are included in the
count condition as well.

Sorry about that,


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


This email proudly and graciously contributes to entropy.