SQL NET - A Place to talk on MS SQL Server and other database tools

Talks on SQL Server, database development, data management techniques,SQLBI/SSIS/SSAS/SSRS etc. Want to publish something db related ? mail to bineeshthomas@gmail.com with subject line starting as "SQLNET"

SQL NET FORUM

Portal Maintained By : BINEESH THOMAS

Tuesday, July 01, 2008

Finding Cumulative total using SQL Query


A table contains

Create Table #tab (SlNo Int, Name char(1),Amt Int, CAmt Int)
Insert Into #tab Values(1,'a',10,Null)
Insert Into #tab Values(2,'b',5,Null)
Insert Into #tab Values(3,'c',8,Null)
Insert Into #tab Values(4,'d',3,Null)
Insert Into #tab Values(5,'e',15,Null)

Display cumulative figure like below using a single query

Slno     Name  Amt CumulativeAmt
1        a        10           10
2        b        5            15
3        c        8            23
4        d        3            26
5        e        15            41


Solution1 :
Select t1.Slno,t1.name,t1.amt,sum(t2.amt) CumAmount
From #Tab t1,#Tab t2 Where t1.slno>=t2.slno
group by t1.slno,t1.name,t1.amt

Solution 2:
Select  Slno, ( Select
sum(t2.amt)  From ,#Tab T2 where T2.Slno <= T1.Slno )
from  #
Tab T1

 

 

0 Comments:

Post a Comment

Links to this post:

Create a Link

<< Home

Free Domain Name - www.YOU.co.nr!