Cumulative value with data grouping

Question

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;

Answer 1

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;