I'm trying to run a simple query to sum sales by day of the week, the only issue I'm having is the date column is formatted as datetime, not allowing me to simply group by date. Query I'm using:
CREATE TABLE Sales (
Date datetime,
Sales decimal(18,2)
);
INSERT INTO Sales(Date, Sales)
VALUES
('2023-03-30 14:47:36.270', 6.66),
('2023-03-30 19:26:16.223', 7.18);
SELECT
DATEPART(dw, Date) AS WeekDays,
SUM(Sales) AS Total_Sales
FROM Sales
GROUP BY Date;
Current result is:
weekdays | total |
---|---|
5 | 6.66 |
5 | 7.18 |
Desired:
weekdays | total |
---|---|
5 | 13.84 |
dbfiddle example with datetime: https://dbfiddle.uk/6Pw82lJC
dbfiddle example without datetime: https://dbfiddle.uk/ZRTZjh70
You just need to group by
the same thing as you are selecting, because Date
is actually a DateTime
and contains a time component e.g.
SELECT
DATEPART(weekday, Date) AS WeekDays
, SUM(sales) AS Total_Sales
FROM Sales
GROUP BY DATEPART(weekday, date);
And do yourself a favour, don't use the abbreviated forms of datepart (e.g. dw
)... for the sake of a few letters make it super clear what you are doing.