Sum of total by date with running total

1
votes

I have an orders table with each row having :

  • Order id
  • Date the order was checked out
  • Order transaction value

I am looking to group by date (yyyy-mm-dd) the transaction value for that period but I also want to return a running total for the period. If I ran this query against this months data (13 days in to November), I would expect to see a result set returned

  • 13 rows, one for each day of the month,
  • Transaction value of that day of the month
  • Running Total for the month so far

I can get the transaction value group by date using group by but no running total. If I user over (partition), I am getting 1 row per transaction but the sum of the transaction values for the day.

Currently the code looks like this :

SELECT 
    left(convert(varchar(30),OrderCheckOutDate,120),10), 
    SUM (OrderTotalFixed) 
    OVER (Partition By left(convert(varchar(30),OrderCheckOutDate,120),10))
FROM 
    tblOrder
Where
    UserExternalUserLevel = 0 
And
    OrderCheckOutDate > '2019-11-01'

It's as if the query is partitioning by millisecond but I thought the convert would handle this?

3
sql-servertsql
Instead of using varchar, you can change the DateTime type to Date: convert(DATE, OrderCheckOutDate) - Jonathan Larouche
That still doesn't seem to group orders of the same day together. - Tfx77
No it won't, I just pointed out that you can use this conversion to get the Date part of a DateTime. See my Answer that solve your issue. - Jonathan Larouche
i think it was better if you share current result and expected result. - UsmanMirza

3 Answers

2
votes

To group by Date, you don't need the OVER (Partition By) and put the column under the Group By

Try this:

;WITH TotalByDay AS (
SELECT 
    convert(DATE,OrderCheckOutDate) OrderCheckOutDate ,
    SUM (OrderTotalFixed) OrderTotalFixed 
FROM 
    tblOrder
Where
    UserExternalUserLevel = 0 
And
    OrderCheckOutDate > '2019-11-01'
GROUP BY  convert(DATE,OrderCheckOutDate)
)

SELECT 
   OrderCheckOutDate,
   (SELECT SUM(TotalByDay) FROM TotalByDay innerT where innerT.OrderCheckOutDate <= T.OrderCheckOutDate) RunningTotal
FROM TotalByDay t
GROUP BY t.OrderCheckOutDate
2
votes

Here's a version that squeezes the size of the code down compared to other examples. It should also be a little faster than some as there's no sub queries forcing multiple reads of the table. The trick to remember when using running totals and a group by statement is that you don't apply the window function to the raw column but to an aggregate of the raw column (hence I nest a SUM inside a SUM).

SELECT 
    CAST(OrderCheckOutDate AS date) AS OrderCheckOutDate, 
    SUM (OrderTotalFixed) AS DayTotal,
    SUM (SUM (OrderTotalFixed)) OVER (ORDER BY CAST(OrderCheckOutDate AS date)) AS RunningTotal
FROM 
    tblOrder
Where
    UserExternalUserLevel = 0 
And
    OrderCheckOutDate > '2019-11-01'
GROUP BY CAST(OrderCheckOutDate AS date)

You can see this working at dbfiddle

If your actual use case is more complicated for example you want the running total to reset each month that can be handled by adding PARTITION in to the window function.

Partitioning by month will look something like this:

SELECT 
    CAST(OrderCheckOutDate AS date) AS OrderCheckOutDate,
    EOMONTH(CAST(OrderCheckOutDate AS date)) AS MonthEnd,
    SUM (OrderTotalFixed) AS DayTotal,
     SUM (SUM (OrderTotalFixed)) OVER (PARTITION BY EOMONTH(CAST(OrderCheckOutDate AS date)) ORDER BY CAST(OrderCheckOutDate AS date) ) AS RunningTotal
FROM 
    tblOrder
Where
    UserExternalUserLevel = 0 
And
    OrderCheckOutDate > '2019-11-01'
GROUP BY CAST(OrderCheckOutDate AS date)

And again see it in action with dbfiddle

1
votes

Here is a similar one I have done, hopefully you can adjust for your tables / needs. The inner query (TOTALS_Q) simply gets the sales & counts by month. The outer query also gets the cumulative.

The important bit for the cumulatives is the "ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW"

SELECT 
  SaleMonth, Month_Order_Count, MonthTotal,   
  SUM(Month_Order_Count) OVER(ORDER BY SaleMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumulative_Order_Count,
  SUM(MonthTotal) OVER(ORDER BY SaleMonth ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Cumulative_Order_Value
FROM 
(
  SELECT SUB_Q.SaleMonth, Count( DISTINCT SUB_Q.OrderID) as Month_Order_Count, SUM(Value) as MonthTotal  FROM
  (
    SELECT CONCAT( DatePart( year, OH.OrderDate), '-', FORMAT( OH.OrderDate, 'MM')) as SaleMonth,
    OI.OrderID,
    OI.Value
  FROM ORDERHEADER OH 
  INNER JOIN OrderItem OI ON OI.OrderID = OH.OrderID
  INNER JOIN ECodeOrder ECO ON ECO.OrderId = OH.ORDERID
  INNER JOIN Product P ON P.ProductId = OI.ProductId AND P.Denomination >= 10000
  ) SUB_Q
  GROUP BY SUB_Q.SaleMonth
)TOTALS_Q
ORDER BY SaleMonth