CODEWITHSUNDEEP
sql-servert-sql
Tfx77
Tfx77

Reputation: 25

Sum of total by date with running total

I have an orders table with each row having :

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

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?

Upvotes: 1

Views: 3168

Answers (3)

BarneyL
BarneyL

Reputation: 1362

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

Upvotes: 3

AntDC
AntDC

Reputation: 1907

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

Upvotes: 1

Jonathan Larouche
Jonathan Larouche

Reputation: 992

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

Upvotes: 2

Related Questions