CODEWITHSUNDEEP
mysqldatabase
Javi Prieto
Javi Prieto

Reputation: 429

Count weekdays occurrences when some days are empty mysql

I have table with some events and their dates in a mysql database and I wanna return a counting of the events per weekday like this:

SELECT DAYNAME(start_date) AS day, count(DAYNAME(start_date)) as number FROM events GROUP BY day

That works great for days in which occur some events, but it doesn't show the empty days. I know if I would have a simple table with days of the week I could do a simple LEFT JOIN and show ALL days, with a count = 0 when the day is empty. But is there any simpler way to do that without creating that table? Any other ideas?

Thanks in advance

Upvotes: 0

Views: 351

Answers (2)

Johan
Johan

Reputation: 76537

SELECT 
  COALESCE(DAYNAME(start_date),'empty') AS day
  , count(start_date) as number 
FROM events
RIGHT JOIN (SELECT 0 as dy FROM DUAL UNION ALL 
            SELECT 1 as dy FROM DUAL UNION ALL
            SELECT 2 as dy FROM DUAL UNION ALL
            SELECT 3 as dy FROM DUAL UNION ALL
            SELECT 4 as dy FROM DUAL UNION ALL
            SELECT 5 as dy FROM DUAL UNION ALL
            SELECT 6 as dy FROM DUAL UNION ALL
            SELECT 'empty' as dy FROM DUAL) ds 
        ON (ds.dy = COALESCE(DAYNAME(start_date),'empty'))
GROUP BY ds.dy

Upvotes: 1

Tom Mac
Tom Mac

Reputation: 9853

In a word 'no'. Reason being that you are trying to select data that does not exist!

However, since there are only 7 days in a week you could construct your simple 'Days Of The Week' table on the fly:

select dayname('2011-10-31') union
select dayname('2011-11-01') union
select dayname('2011-11-02') union
select dayname('2011-11-03') union
select dayname('2011-11-04') union
select dayname('2011-11-05') union
select dayname('2011-11-06');

And then LEFT OUTER JOIN from that onto the events table (as you have described). This will allow you to run a query without having to create and populate the simple table.

Though for the sake of reuse and tidiness I would create and populate the simple table with days of the week and use that.

Upvotes: 1

Related Questions