mysql order by dayname

mysql, php

Solution

You need to add the function `DAYOFWEEK` to your `GROUP BY` and `ORDER BY`:

GROUP BY DAYOFWEEK(dl.bookingDate)
ORDER BY DAYOFWEEK(dl.bookingDate)

You may also want to look into using explicit ANSI standard `JOIN` syntax.

SELECT 
    DAYNAME(dl.bookingDate) AS dayName, COUNT(dg.idDiary) AS totalBooked
FROM
    diary_list dl
JOIN diary_group dg
    ON dl.idDiary = dg.idDiary
WHERE
    dg.idService != 0 AND dl.status = 3
GROUP BY 
    DAYOFWEEK(dl.bookingDate)
ORDER BY 
    DAYOFWEEK(dl.bookingDate)

Problem

how do I order in mysql by day name ie: Sunday, Monday, Tuesday, Wednesday so on ``` SELECT DAYNAME(dl.bookingDate) AS dayName, COUNT(dg.idDiary) AS totalBooked FROM diary_list dl, diary_group dg WHERE dl.idDiary = dg.idDiary AND dg.idService != 0 AND dl.status = 3 GROUP BY dayName ORDER BY dayName ??? ```

Original source