CODEWITHSUNDEEP
sqlsql-serverdatedatetimegroup-by
Brandon Brown
Brandon Brown

Reputation: 29

SQL Group dates by day of week and get count

I have a table with a bunch of dates in it and I need to convert those dates to what day of the week it is and then group them.

Example output looks like

+------------+-------+
|    Date    | Count |
+------------+-------+
| 11/12/2018 |     1 |
| 11/19/2018 |     2 |
| 11/20/2018 |     1 |
| 11/21/2018 |     2 |
+------------+-------+

The output that I would like would be

+-----------+-------+
|  DayName  | Count |
+-----------+-------+
| Monday    |     3 |
| Tuesday   |     1 |
| Wednesday |     2 |
+-----------+-------+

Im just unsure on how to rename the dates to the day of the week and have them group together.

Upvotes: 0

Views: 1933

Answers (1)

Salman Arshad
Salman Arshad

Reputation: 272006

Use the datename function to extract the weekday "name":

SELECT DATENAME(WEEKDAY, Date), COUNT(*)
FROM t
GROUP BY DATENAME(WEEKDAY, Date)

Upvotes: 8

Related Questions