Group by weekday name

Question

User2079058187 posted

Can someone help in producing the following results from a table which has a timestamp field which is to be grouped by on the weekday name:

DAY           TOTAL
Monday       5
Tuesday      1
Wednesday 8
Thursday     4
Friday         7
Saturday     0
Sunday       9

Answer 1

User1508394307 posted

SELECT DATENAME(dw, SavedOn), Count(*)
FROM myTable
GROUP BY DATENAME(dw, SavedOn), DatePart(WEEKDAY, SavedOn) 
ORDER BY DatePart(WEEKDAY, SavedOn) 

Answer 2

User2079058187 posted

Sorry, I'm new to MySql. What's SavedOn?

Answer 3

User1508394307 posted

I didn't know it's MySql, sorry.

Try

SELECT DAYOFWEEK(name_of_datetime_column), count(1)
FROM tablename
GROUP BY 1;

Answer 4

User2079058187 posted

It works but comes p with 1,2..7 instead of day names

Answer 5

User1508394307 posted

I didn't test it, but it must be similar to the following

SELECT DAYOFWEEK(name_of_datetime_column), DAYNAME(name_of_datetime_column), count(1)
FROM tablename
GROUP BY 1;

or 

SELECT DAYNAME(name_of_datetime_column), count(1)
FROM tablename
GROUP BY 1
ORDER BY DAYOFWEEK(name_of_datetime_column);
  
or

SELECT day_of_week, order_count FROM 
(
  SELECT DAYNAME(name_of_datetime_column) day_of_week, 
         DAYOFWEEK(name_of_datetime_column) day_num, 
         count(*) order_count
  FROM tablename 
  GROUP BY 1
) temp
GROUP BY day_of_week 
ORDER BY day_num

Answer 6

User2079058187 posted

"GROUP BY 1" in the last query, does that group by the first column?