Home Spliting a week of data in to days of the Week SQL
 Good Morning All So I was asked to look where things are getting ordered from on a weekly bases so simple. DECLARE @vYearWeek INT SET @vYearWeek = '201745'; SELECT LEFT(Orders.ProductWhsLocation,1) AS 'Area', COUNT(Orders.HostOrderNo) AS 'Picked' FROM CHDS_Common.dbo.OMOrder Orders LEFT JOIN CHDS_Management.dbo.Calendar Cal ON CONVERT(date, Orders.EarliestPickDate) = Cal.DT WHERE Orders.PicksetNo <> 0 AND cal.YWK = @vYearWeek GROUP BY LEFT(ProductWhsLocation,1) ORDER BY LEFT(ProductWhsLocation,1);  And this works fine ... how ever I need to got deeper now and spilt it in to days. Now logic would say this will be simple as: DECLARE @vYearWeek INT SET @vYearWeek = '201745'; SELECT LEFT(Orders.ProductWhsLocation,1) AS 'Area', CASE WHEN Cal.DW = 1 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Sunday', CASE WHEN Cal.DW = 2 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Monday', CASE WHEN Cal.DW = 3 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Tuesday', CASE WHEN Cal.DW = 4 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Wednesday', CASE WHEN Cal.DW = 5 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Thuresday', CASE WHEN Cal.DW = 6 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Friday', CASE WHEN Cal.DW = 7 THEN COUNT(Orders.HostOrderNo) ELSE 0 END AS 'Saturday' FROM CHDS_Common.dbo.OMOrder Orders LEFT JOIN CHDS_Management.dbo.Calendar Cal ON CONVERT(date, Orders.EarliestPickDate) = Cal.DT WHERE Orders.PicksetNo <> 0 AND cal.YWK = @vYearWeek GROUP BY LEFT(ProductWhsLocation,1) ORDER BY LEFT(ProductWhsLocation,1);  But as you all know ERROR :( Column 'CHDS_Management.dbo.Calendar.DW' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Any Help on sorting this out would be great many thanks all.