# Thread: Calculating Running Totals Department wise???

1. Registered User
Join Date
Nov 2007
Posts
41

## Unanswered: Calculating Running Totals Department wise???

Hi Friends,

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

Any help would be greatly appreciated!

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 correct 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
*/

2. Registered User
Join Date
Mar 2009
Location
Australia
Posts
113
Simple change...

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 a.deptno = b.deptno) as "RunningTotalDept"
from cte as a
Last edited by EngadaSQL; 06-17-09 at 02:00.

3. Registered User
Join Date
Nov 2007
Posts
41
Thank You so much!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•