0

I need some expertise help with a following query. So, I need to make a cumulative calculation per day based on the GMV amount.

My biggest problem here is I can't remove the gmv from the GROUP BY or I get some error.

Any ideas on how I can solve this?

SELECT CONVERT(varchar, order_date, 103) AS date,
       DATEPART(WEEKDAY, order_date) AS weekday,
       SUM(gmv) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS cumulative
FROM database
WHERE order_date BETWEEN '2017-02-01' AND '2017-08-01'
GROUP BY order_date,
         gmv;
giujong
  • 11
  • 1

1 Answers1

3

As I mentioned in the comments, first use a derived table to get your aggregates in your groups, and then do your cumulative SUM:

WITH CTE AS
    (SELECT CONVERT(date, order_date) AS OrderDate, --I assume here that Order_date is a date and time value
            SUM(GMV) AS GMV
     FROM dbo.Raw_data
     GROUP BY CONVERT(date, order_date))
SELECT OrderDate,
       DATEPART(WEEKDAY, OrderDate) AS WeekDay,
       SUM(GMV) OVER (ORDER BY OrderDate ASC
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CumulativeGMV
FROM CTE;
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • Or you could use `CROSS APPLY ( SELECT CONVERT(date, order_date) )` and avoid the CTE. Also `ROWS UNBOUNDED PRECEDING` is the same but shorter version of `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` – Charlieface Feb 03 '22 at 13:50
  • Technically the boundary isn't needed at all, @Charlieface , as `ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW` is the default boundary when an `ORDER BY` is added; I just prefer to be explicit. – Thom A Feb 03 '22 at 13:55
  • It's not, the default is `RANGE UNBOUNDED PRECEDING` which is subtly different, has poorer performance and trips everyone up because it looks the same unless you have a non-unique ordering. I don't know, `ROWS UNBOUNDED PRECEDING` looks explicit to me, whatever floats your boat I suppose. +1 anyway – Charlieface Feb 03 '22 at 14:19