Conditional counting MySQL

Question

I have to fetch weekly report from a table having every record their status like 1 is for open and 2 is for closed, So am using week as an group by key and using case conditions for diff counts like open,closed,red etc but data isn't accurate as i want. You will better under stand by below mentioned query. Every response appropriated.

SELECT DISTINCT discussion_date,department,DAYNAME(discussion_date) AS DAY,CONCAT(YEAR(discussion_date),'/',WEEK(discussion_date)) AS week_name,YEAR(discussion_date) AS YEAR,CONCAT('WEEK -', WEEK(discussion_date)) AS week_no,COUNT(*) as total_queries,(CASE WHEN review_items.status = 1 AND due_date > curdate() THEN count(*) ELSE 0 END) as open,(CASE WHENreview_items.status = 2 THEN count(*) ELSE 0 END) as closed,(case when manager_closuredate > due_date THEN count(*) ELSE 0 END) as red,(CASE WHEN review_items.status = 3 THEN count(*) ELSE 0 END) as hold,(CASE WHEN review_items.status = 4 THEN count(*) ELSE 0 END) as discussion,DATE_ADD(discussion_date,INTERVAL(2 - DAYOFWEEK(discussion_date)) DAY) AS Week_start_date,DATE_ADD(discussion_date,INTERVAL(7 - DAYOFWEEK(discussion_date)) DAY) AS Week_end_date,DAYNAME(DATE_ADD(discussion_date,INTERVAL(2 - DAYOFWEEK(discussion_date)) DAY)) AS WeeK_Start_DAY FROM review_items
WHERE department = 'Development' AND discussion_date BETWEEN '2017-08-01' AND '2017-08-31'
GROUP BY week_name
ORDER BY YEAR (discussion_date) ASC,WEEK(discussion_date) ASC

score 1 · Answer 1 · answered Sep 14 '17 at 11:55

You are not using the conditional aggregation correctly:

COUNT(CASE WHEN review_items.status = 1 AND due_date > curdate() THEN 1 END) as open,
COUNT(CASE WHEN review_items.status = 2 THEN 1 END) as closed,
COUNT(case when manager_closuredate > due_date THEN 1 END) as red,
COUNT(CASE WHEN review_items.status = 3 THEN 1 END) as hold,
COUNT(CASE WHEN review_items.status = 4 THEN 1 END) as discussion,

You can both COUNT and use a value, the column is either an aggregation fucntion, or not, not both.

So the idea is to add 1 to the counter, only if the condition is met. There are several ways of doing it, one of whom is with COUNT() , if the condition is met, then it's evaluate as COUNT(1) which is 1. If not, it evaluate as COUNT(NULL) , which is 0 .

You can also use SUM() :

SUM(CASE WHEN <Condition> THEN 1 ELSE 0 END)

Conditional counting MySQL

1 Answers1