Sql Running Total Grouped By Date

(Using SQL Server 2008) I could easily get this to work if I built a view, and then a query on the view, but I want to perform this in one SQL query. I have a table that contains t

Solution 1:

If you are using a product that implements ordered OVER clauses for SUM, you can do something like this:

select
  cast(DeliveredDate asdate) as DeliveredDate,
  count(Delivered) as Delivered,
  sum(count(Delivered)) over (
    orderby DeliveredDate
  ) as RunningTotal
from Orders
groupby OrderDate
orderby OrderDate;

Your expression count(Delivered) is a bit strange. Just to be sure it's what you want: it will count the number of rows on the particular date for which the value in the Delivered column is NOT NULL.

Solution 2:

I'm not sure if a CTE counts as a view but this will work in SQL 2005+ which Does Not Support ordered OVER clauses for SUM,

WITH cte (DeliveredDate, Delivered)
     AS (SELECT Cast([DeliveredDate] ASDATE) DeliveredDate, 
                Count([Delivered]) Delivered 
         FROM   [TableName] 
         GROUPBY Cast([DeliveredDate] ASDATE)) 
SELECT d1.delivereddate, 
       d1.delivered, 
       Sum(d2.Delivered) RunningTotal 
FROM   cte d1 
       LEFT JOIN cte d2 
         ON d1.delivereddate >= d2.DeliveredDate 
GROUPBY d1.delivereddate, 
          d1.delivered 
ORDERBY d1.delivereddate