I am trying to get the sales of a week formatted by days of the week, the problem is that my query behaves like a normal Sum() , ignoring sales from different weeks of the same days. In the example database I am inserting two different sales on two different weeks and the same day of the week resulting in a single row of 38.92, instead of two rows with the sales according to that day of the week.
This is the query I'm using:
SELECT
CASE WHEN weekdays = 1 THEN Total_Sales ELSE 0 END as Sunday,
CASE WHEN weekdays = 2 THEN Total_Sales ELSE 0 END as Monday,
CASE WHEN weekdays = 3 THEN Total_Sales ELSE 0 END as Tuesday,
CASE WHEN weekdays = 4 THEN Total_Sales ELSE 0 END as Wednesday,
CASE WHEN weekdays = 5 THEN Total_Sales ELSE 0 END as Thursday,
CASE WHEN weekdays = 6 THEN Total_Sales ELSE 0 END as Friday,
CASE WHEN weekdays = 7 THEN Total_Sales ELSE 0 END as Saturday
FROM
(SELECT
DATEPART(WEEKDAY, date) AS Weekdays,
SUM(sales) AS Total_Sales
FROM Sales
WHERE CAST(Date AS date) BETWEEN '2023-03-01' AND '2023-03-10'
GROUP BY DATEPART(weekday, Date)) XCurrent result:
| Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
|---|---|---|---|---|---|---|
| 30.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 0.00 | 6.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 38.92 | 0.00 |
| 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 40.55 |
Desired:
| Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday |
|---|---|---|---|---|---|---|
| 30.00 | 0.00 | 0.00 | 0.00 | 0.00 | 13.84 | 0.00 |
| 0.00 | 6.00 | 0.00 | 0.00 | 0.00 | 25.08 | 0.00 |
| 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
| 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 40.55 |
Dbfiddle example: https://dbfiddle.uk/r-Qg8JDT
1条答案
按热度按时间xpcnnkqh1#
It seems to be ok for that friday.
Dbfiddle example: https://dbfiddle.uk/MbCfqFVY