I'm trying to count the number of times multiple words appears in a column named sg_event but currently run each one separately. Does anyone know how I can combine this into one query.
select count(*) from metrics
WHERE sg_event = 'open';
select count(*) from metrics
WHERE sg_event = 'delivered';
select count(*) from metrics
WHERE sg_event = 'click';
My desired outcome is as follows,
open_count, delivered_count, click_count
You can use conditional sum for this
select
sum( case when sg_event = 'open' then 1 else 0 end ) as `open_count`,
sum( case when sg_event = 'delivered' then 1 else 0 end ) as `delivered_count`,
sum( case when sg_event = 'click' then 1 else 0 end ) as `click_count`
from metrics
You can use the standard sql feature group by:
SELECT count(*), sg_event
FROM metrics
WHERE sg_event IN ( 'open', 'delivered', 'click')
GROUP BY sg_event;
You should get something like
10 open
15 delivered
76 click
so each line get one sum.
Of course you can sort your results (e.g.: ORDER BY 1, to sort by count column)
This solution is more flexible, cause you can specify a lot more values to inspect (here for sg_event), maybe a join with a table which hold all your interested sg_events.
As shown in the answer by Abhik Chakraborty you can use the caseexpression to conditionally aggregate data, but since MySQL evaluates boolean expressions as 1 or 0 you can reduce the query further to this more compact form:
select
sum(sg_event = 'open') as `open_count`,
sum(sg_event = 'delivered') as `delivered_count`,
sum(sg_event = 'click') as `click_count`
from metrics
