Query for Running Totals with respect to dept's

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query for Running Totals with respect to dept's

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-06-16 : 23:11:54

Hi Friends,

I need a report query where in i need to display the running totals w.r.t departments.

Please help me out!

Here is the source data along with my query.

CREATE TABLE EMPP
(EMPNO INT,
ENAME VARCHAR(20),
SAL INT,
DEPTNO INT
)

INSERT INTO EMPP
SELECT 7840,'MANISH',2000,10
UNION ALL
SELECT 7399,'GAYLE',3000,10
UNION ALL
SELECT 5389,'SIDHU',7000,20
UNION ALL
SELECT 7690,'ARVIND',3000,20
UNION ALL
SELECT 9390,'AJAY',8000,10
UNION ALL
SELECT 9373,'KALYAN',2000,20
UNION ALL
SELECT 6399,'RAMA',5000,30
UNION ALL
SELECT 8390,'MAHER',6000,30
UNION ALL
SELECT 9300,'RAVI',3000,30

with cte
as
(
select
row_number() over(order by deptno) as "seq_no"
,row_number() over(partition by deptno order by deptno) as "seq_no_deptno"
,empno
,ename
,deptno
,sal
from empp a
)
select a.seq_no
,a.seq_no_deptno
,a.empno
,a.ename
,a.deptno
,a.sal
,(select sum(b.sal) from cte b where b.seq_no <=a.seq_no) as "RunningTotal"
from cte as a

-- output
/* sal runningTotal
1 1 7840 MANISH 10 2000 2000
2 2 7399 GAYLE 10 3000 5000
3 3 9390 AJAY 10 8000 13000
4 1 9373 KALYAN 20 2000 15000
5 2 5389 SIDHU 20 7000 22000
6 3 7690 ARVIND 20 3000 25000
7 1 6399 RAMA 30 5000 30000
8 2 8390 MAHER 30 6000 36000
9 3 9300 RAVI 30 3000 39000
*/

-- The above is right, but my other requirement is , can i get running the w.r.t. departments

-- Typically, my output should look like below one : i need add a new column "RunningTotalWRTDept" to the output

/*
seq_no seq_no_deptno empno ename deptno sal runningTotal RunningTotalWRTDept
1 1 7840 MANISH 10 2000 2000 2000
2 2 7399 GAYLE 10 3000 5000 5000
3 3 9390 AJAY 10 8000 13000 13000
4 1 9373 KALYAN 20 2000 15000 2000 -- this is reseted for deptno=20 observe!!!
5 2 5389 SIDHU 20 7000 22000 9000
6 3 7690 ARVIND 20 3000 25000 12000
7 1 6399 RAMA 30 5000 30000 5000 -- this is again resetted for dept=30
8 2 8390 MAHER 30 6000 36000 11000
9 3 9300 RAVI 30 3000 39000 14000
*/

Thanks in advance.

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-06-16 : 23:29:45

[code]with cte
as
(Select row_number() over(order by deptno) as "seq_no"
,row_number() over(partition by deptno order by deptno) as "seq_no_deptno"
,empno
,ename
,deptno
,sal
from empp a)

select a.seq_no
,a.seq_no_deptno
,a.empno
,a.ename
,a.deptno
,a.sal
,(select sum(b.sal) from cte b where b.seq_no <=a.seq_no) as "RunningTotal"
,(select sum(b.sal) from cte b where b.seq_no <=a.seq_no and b.deptno=a.deptno) as "RunningTotalWRTDept"
from cte as a [/code]

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2009-06-17 : 02:23:26

Thank You!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources