The Busy Pixel

Mysql Totals By Day of Week

06:51:03 am on October 21, 2011 | # | Comments Off

this took me a while to figure out this snippet of sql, if anyone has a better way to do it let me know:

SELECT count(*) as `total`,
dayname(created) as `dayname`,
dayofweek(created) as `weekday`
FROM leads
WHERE date(created) between
DATE_ADD(DATE(NOW()), INTERVAL(1-DAYOFWEEK(DATE(NOW()))) DAY)
AND
DATE_ADD(DATE(NOW()), INTERVAL(7-DAYOFWEEK(DATE(NOW()))) DAY)
GROUP BY dayname(created)
ORDER BY weekday

prints out a table like

Sunday 1

Monday 14

Tuesday 32

etc.