1

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 result of weekday as 0 on which no record has been entered.

From suggestions I come to know about JOIN query. So I have tried to fix it but not getting any solution.

Improve this question

1 Answer 1

Reset to default
4

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 (using UNION inside a SUBQUERY) 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
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks, this works for me. However, is there a way to force fill "0"s for missing days if I use my previous query? I ask this because if I've to fetch this data for a month, my query's going to become an essay.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.