locked
Group by weekday name RRS feed

  • 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
    Monday, September 12, 2011 3:49 PM

All replies

  • User1508394307 posted
    SELECT DATENAME(dw, SavedOn), Count(*)
    FROM myTable
    GROUP BY DATENAME(dw, SavedOn), DatePart(WEEKDAY, SavedOn)
    ORDER BY DatePart(WEEKDAY, SavedOn)
    Monday, September 12, 2011 3:58 PM
  • User2079058187 posted

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

    Monday, September 12, 2011 4:00 PM
  • User1508394307 posted

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

    Try

    SELECT DAYOFWEEK(name_of_datetime_column), count(1)
    FROM tablename
    GROUP BY 1;
    Monday, September 12, 2011 4:07 PM
  • User2079058187 posted

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

    Monday, September 12, 2011 4:11 PM
  • 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

    Monday, September 12, 2011 4:56 PM
  • User2079058187 posted

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

    Thursday, September 15, 2011 7:08 AM