I want to retrieve data of last 1 week from
emp_info table on per day basis.
So I have used :
SELECT DAYNAME(timestamp), COUNT(*) FROM `emp_info` WHERE DATE(timestamp ) > DATE_SUB(CURDATE( ) , INTERVAL 1 WEEK ) GROUP BY DAYNAME(timestamp);
According to the query I am getting result like:
Monday 5 Thursday 7
But I also want the <strong>result</strong> of weekday as <strong>0</strong> on which no record has been entered.
From suggestions I come to know about <strong>JOIN</strong> query. So I have tried to fix it but not getting any solution.Answer1:
The result you are getting is right because there are no records on a specific dayname. Since you want to get all daynames, you need to project complete set of day (<em>using UNION inside a SUBQUERY</em>) and join it with your existing query.
SELECT a.day_name, COALESCE(b.totalCount, 0) totalCount FROM ( SELECT 'Sunday' day_name, 1 ordby UNION ALL SELECT 'Monday' day_name, 2 ordby UNION ALL SELECT 'Tuesday' day_name, 3 ordby UNION ALL SELECT 'Wednesday' day_name, 4 ordby UNION ALL SELECT 'Thursday' day_name, 5 ordby UNION ALL SELECT 'Friday' day_name, 6 ordby UNION ALL SELECT 'Saturday' day_name, 7 ordby ) a LEFT JOIN ( SELECT DAYNAME(timestamp) day_name, COUNT(*) totalCount FROM `emp_info` WHERE DATE(timestamp ) > DATE_SUB(CURDATE( ) , INTERVAL 1 WEEK ) GROUP BY DAYNAME(timestamp) ) b ON a.day_name = b.day_name ORDER BY a.ordby<ul><li><a href="http://www.sqlfiddle.com/#!2/3d813/1" rel="nofollow">SQLFiddle Demo (<em>simple example</em>)</a></li> </ul>