MySQL Group data by name of day in last week

Question

I am trying to write a query to group the last 7 days worth of stats data by weekday and return the following:

    dayname | count(*)
-----------------------
Monday      |   3              
Tuesday     |   2        

The above is my desired output however I can't seem to get the dayname.

    Day(enquiries.created_at) | Count(enquiries.id)
-----------------------------------------------------
    1                         | 1
    3                         | 1
    4                         | 14
    31                        | 5

The above is my current output and an explanation is that 31st August, 1st September etc... however I want to group by weekday name as opposed to date.

Here is the query that I have so far:

SELECT Day(enquiries.created_at), 
       Count(enquiries.id) 
FROM   enquiry_stats 
       LEFT JOIN enquiries 
              ON enquiry_stats.enquiry_id = enquiries.id 
WHERE  created_at >= Date_add(Curdate(), INTERVAL -7 day) 
GROUP  BY Day(enquiries.created_at);

I am just wondering how to group the data not by day but by dayname?

Answer 1

"I am just wondering how to group the data not by day but by dayname?"

As easy as it will sound, use DAYNAME() instead:

SELECT DayName(enquiries.created_at), 
       Count(enquiries.id) 
FROM   enquiry_stats 
       LEFT JOIN enquiries 
              ON enquiry_stats.enquiry_id = enquiries.id 
WHERE  created_at >= Date_add(Curdate(), INTERVAL -7 day) 
GROUP  BY DayName(enquiries.created_at);