Click here to Skip to main content
15,919,422 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I have a problem regarding on sum function of SQL

I have a raw data like this :

Count	Date	Row
10	1/1/2013	1
10	1/2/2013	2
10	1/3/2013	3


Now i want this to sum up :

Sum Date
10  1/1/2013
20  1/2/2013
30  1/3/2013


This is my current query :

SQL
declare @count int
declare @i int
set @i = 1
set @count = (select max(Row) from #test) -- equals to 3

while @i <= @count
begin
	select sum(Count) as SumCount
		, Date
	from #test
	where row between 1 and @i
	group by Date
	set @i = @i + 1
end


However the result was like this :

SumCount   Date
10	   1/1/2013
10	   1/2/2013
10	   1/3/2013


I figured out that it was the grouping per Date that i was having the problem,
plese help me on this thanks!
Posted
Updated 3-Nov-13 22:15pm
v2

1 solution

What you want is called "running total". If you search google with this keywords, you will find several articles.
Better start here: Calculating simple running totals in SQL Server[^], and here: http://www.sqlperformance.com/2012/07/t-sql-queries/running-totals[^]
Please note the performance and resource considerations made by the authors. It is not as straightforward as it looks compared with an imperative approach you might already know.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900