SQL Running Total Grouped by date
(Using SQL Server 2008) I could easily get this to work if I were to build the view and then the query in the view, but I want to accomplish this in a single SQL query. I have a table that contains two columns (DeliveredDate (DateTime), Delivered (Varchar)). I first convert DeliveredDate to Date only and then I group by date. At the same time, I am doing counting (column is either YES or NULL). Here's what I use to accomplish this part:
SELECT CAST([DeliveredDate] As Date),
COUNT([Delivered])
FROM [TableName]
GROUP BY CAST([DeliveredDate] As Date)
ORDER BY CAST([DeliveredDate] As Date)
As a result, I get something like:
DeliveredDate | Delivered
2012-04-24 10
2012-04-25 500
2012-04-26 422
2012-04-27 33
I'm looking for something like this:
DeliveredDate | Delivered | RunningTotal
2012-04-24 10 10
2012-04-25 500 510
2012-04-26 422 932
2012-04-27 33 965
I've tried various examples I've seen there, but none of them fit this scenario of executing Count and RunningTotal for the specified Count.
source to share
I'm not sure if CTE counts as a view, but this will work in SQL 2005+, which doesn't support ordered OVER clauses for SUM.
WITH cte (DeliveredDate, Delivered)
AS (SELECT Cast([DeliveredDate] AS DATE) DeliveredDate,
Count([Delivered]) Delivered
FROM [TableName]
GROUP BY Cast([DeliveredDate] AS DATE))
SELECT d1.delivereddate,
d1.delivered,
Sum(d2.Delivered) RunningTotal
FROM cte d1
LEFT JOIN cte d2
ON d1.delivereddate >= d2.DeliveredDate
GROUP BY d1.delivereddate,
d1.delivered
ORDER BY d1.delivereddate
source to share
If you are using a product that implements ordered OVER clauses for SUM, you can do something like this:
select
cast(DeliveredDate as date) as DeliveredDate,
count(Delivered) as Delivered,
sum(count(Delivered)) over (
order by DeliveredDate
) as RunningTotal
from Orders
group by OrderDate
order by OrderDate;
Your expression count (Delivered) is a little weird. Just to be sure what you want: it will count the number of rows on a particular date for which the value in the "Supplied" column is not NULL.
source to share