Running total for group (SQL Server)


Here is an question about running total: http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=4136119&SiteID=1&mode=1

 

CREATE TABLE #temp(col1 char(1),col2 int)

insert

into #temp

select

‘A’, 100

union all

select ‘A’, 200

union all

select ‘A’, 300

union all

select ‘B’, 400

union all

select ‘B’, 500

union all

select ‘C’, 600

select

a.col1, a.col2, SUM(b.col2) as gTotal FROM #temp a

cross

join #temp b

WHERE

a.col1=b.col1 and b.col2<=a.col2

GROUP

BY a.col1,a.col2

ORDER

BY a.col1,a.col2

 –Or

SELECT

col1, col2, (SELECT SUM(b.col2) FROM #temp b WHERE a.col1=b.col1 AND b.col2 <= a.col2) AS gTotal FROM #temp AS a

ORDER

BY col1, col2;

 

drop

table #temp

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s