25 November, 2009

Cumulative totals Query with T-SQL

The following query will be Very useful when one want to calculate the Cumulative total in SQL Server.

For Ex:
Create Table TEST_DATA
{
ID Int NOT NULL,
Amount Int NOT NULL
}

if the table data is like

ID  Amount
--- ------
1 10
2 20
3 40
4 50
5 30

select t1.ID, t1.Amount,
(select sum(t2.Amount ) from
TEST_DATA as t2 where t2.ID <= t1.ID) as Total from TEST_DATA as t1

This Query will return the results as

ID Amount Total
--- ------ -----
1 10 10
2 20 30
3 40 70
4 50 120
5 30 150

-Happy Querying :)

No comments:

Post a Comment