CODEWITHSUNDEEP
sqlsql-servert-sql
dazzathedrummer
dazzathedrummer

Reputation: 531

Running total with Over

I'm trying to create a running total of the number of files per opened by day so I can use the data for a graph showing cumulative results.

The data is basically the file opening date, a calculated field showing 'This month' or 'Last Month' depending on the date and the running total field that I'm trying to figure out.

Date       Month      Count
====       =====      =====
2019-08-01 Last Month  6
2019-08-02 Last Month  2 
2019-08-03 Last Month  5

I want to have a running total...so 6, 8, 13 etc

But all I'm getting is a row count (1,2,3 etc) for my count field.

select 
FileDate,

Month,
sum(Count) OVER(PARTITION BY month order by Filedate) as 'Count'

from (
select 
1 as 'Count',
Case
    When month(cast(concat(right(d.var_val,4),substring(d.var_val,4,2),left(d.var_val,2)) as DATE) ) = Month(getdate()) then 'This Month'
    else 'Last Month' 
    end as 'Month'


FROM   data d
left join otherdata m on d.VAR_FileID = m.MAT_FileID
left join otherdata u on m.MAT_Fee_Earner = u.User_ID
left join otherdata br on m.MAT_BranchID = br.BR_ID
WHERE  d.var_no IN ( '1628'  )

and Len(var_val) = 10


)files

where Month(FileDate) in  (MONTH(FileDate()),MONTH(getDate())-1)  
and Year(Filedate) = Year(Getdate())
and Dept = 'Peterborough Property'


group by Month, FileDate, count
GO

I'm assuming I've not quite grasped the proper usage of 'OVER' - any pointers would be great!

Upvotes: 0

Views: 63

Answers (2)

Shekar Kola
Shekar Kola

Reputation: 1297

Hope your clear with OVER clause now (with "Sentinel" answer), in which case you should replace desired column as follows, so that count continuously increase for all the rows from sub-query based on order by clause: for more details on OVER Clause..

sum(Count) OVER (Oder by Filedate) as [Count]
-- or 
sum(Count) OVER (Oder by Filedate desc) as [Count]

Upvotes: 0

Sentinel
Sentinel

Reputation: 6459

The Partition clause indicates when to reset the count, so by partitioning by month you are only counting records for each discreet month to get a running total, over the whole dataset, you don't want the partition clause at all, just the order by clause.

Upvotes: 3

Related Questions