CODEWITHSUNDEEP
t-sqlsql-server-2012
allendks45
allendks45

Reputation: 341

How do I get the Day of Week order to start with Monday

I have a sales table and want to get the day of week for the orders and the number of orders placed on each day of the week. Everything seems to work but no matter what I try the 'week' is non standard. I've tried SET DATEFIRST 1; but still do not get the results I want. 

SELECT DATENAME(Weekday,orderdate) AS Weekday, COUNT(orderid) AS NumOrders
FROM Sales.Orders
GROUP BY DATENAME(Weekday,orderdate);

The results:

 Weekday     |    NumOrders
   1. Wednesday      25
   2. Saturday       33
   3. Monday         100
   4. Sunday         115
   5. Thursday       87
   6. Tuesday        42

Could the no orders for Friday be causing the output not to order by start of week? Thanks.

Upvotes: 0

Views: 181

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

I think you just need an ORDER BY:

SELECT DATENAME(Weekday, orderdate) AS Weekday, COUNT(orderid) AS NumOrders
FROM Sales.Orders
GROUP BY DATENAME(Weekday, orderdate), DATEPART(Weekday, orderdate)
ORDER BY DATEPART(Weekday, orderdate);

Upvotes: 3

Related Questions