SELECT count(*) FROM bossy.talent
where talent.date >= '2019/01/31' and talent.date <= '2019/02/28'
and (country_id = 1 or country_id = 2) and (current_status = 'completed' or current_status = 'incompleted');
This query print 2637 which is the total number of completed and incompleted columns. But I need separate outputs for completed and incompleted like
2600
37
2600 is the number for completed and 37 is the number for incompleted.
You can do this by combination of SUM function with CASE WHEN clause.
For example, if we look at the completed, CASE WHEN will select 1 if current_status = 'completed' and 0 if current_status is anything else. And at the end when you sum that you get a final desired result.(1 + 1 + 0 = 2 for my example).
SELECT sum(case when current_status = 'completed' then 1
else 0
end) completed,
sum(case when current_status = 'incompleted' then 1
else 0
end) incompleted
FROM talent
where date between '2019/01/31' and '2019/02/28'
and country_id in (1, 2);
Here is a small DEMO
Here is how you can do it with UNION so you see one data under the other:
SELECT sum(case when current_status = 'completed' then 1
else 0
end) total_number
FROM talent
where date between '2019/01/31' and '2019/02/28'
and country_id in ( 1, 2)
union
SELECT sum(case when current_status = 'incompleted' then 1
else 0
end) total_number
FROM talent
where date between '2019/01/31' and '2019/02/28'
and country_id in ( 1, 2) ;
Here is the DEMO for this second query.
Note that in this both query's you can use:
date between '2019/01/31' and '2019/02/28'
instead of :
talent.date >= '2019/01/31' and talent.date <= '2019/02/28'
and:
country_id in (1, 2)
instead of :
(country_id = 1 or country_id = 2)
UNION or GROUP BY clauses. If you can edit your answer and add these details as to why, that would be a huge improvement. ThanksUse GROUP BY :
SELECT current_status, count(*)
FROM bossy.talent
WHERE talent.date >= '2019/01/31' AND
talent.date <= '2019/02/28' AND
country_id IN (1,2) AND
current_status IN ('completed', 'incompleted')
GROUP BY current_status;
You can use IN instead of ORs. If you want further bifurcation by country then add it also in GROUP BY.
GROUP BY be used here?
GROUP BY ... WITH ROLLUP here: dev.mysql.com/doc/refman/8.0/en/group-by-modifiers.htmlGROUP BY current_status makes the query return one row for each distinct value of current_status. The COUNT(*) aggregation applies to each group of rows with the same value of current_status.completed 2600, incompleted 37, total 7530. Total is the no of rows in the table.