Tom Nash Tom Nash - 1 year ago 45
SQL Question

How to group based on the count of a string attribute equaling a value?

I have a table and I've managed to narrow it down to this:

Event | Response
------------ | ------
Birthday | Yes
Anniversary | No
Anniversary | No
Birthday | Yes
Birthday | Yes


I want to count the number of 'yes' replies for each event, so the end product should be:

Event | numYes
------------ | ------
Birthday | 3
Anniversary | 0


I imagine it uses
group by Event
and an aggregator for
Response = 'yes'
, but I'm not sure how to make it work

Answer Source

Try this:

select event, sum(Response = 'Yes') numYes
from your_table
group by event;

It uses the fact that true is 1 and false is 0 in MySQL and results in shorter expression.

Another way of achieving the same thing is using COUNT like this:

select event, count(case when Response = 'Yes' then 1 end) numYes
from your_table
group by event;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download