LearnItFirst User Forum

Welcome Guest

Search | New Posts | Members | Log In | Register

SQL Server Forum - LearnItFirst.com » Transact-SQL Programming » DML (SELECT, INSERT, UPDATE, DELETE) Questions » Query for Running Totals with respect to dept's

Query for Running Totals with respect to dept's

Options

Previous Topic · Next Topic

frank.svs

Posted: Tuesday, June 16, 2009 10:19:55 PM

Rank: Newbie

Joined: 11/20/2007
Posts: 7
Points: -79
Where do you live?: india

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

Thanks in advance.

Scott Whigham

Posted: Tuesday, June 30, 2009 8:52:24 AM

Rank: Super Mod

Joined: 3/20/2006
Posts: 466
Points: 1,020
Where do you live?: Dallas, TX

Just add another correlated subquery and correlate on the DEPTNO

Code:

(select sum(b.sal) from cte b where b.seq_no <=a.seq_no AND b.DEPTNO = a.DEPTNO) as "RunningTotalWRTDepartments"

Users browsing this topic

Guest

Forum Jump

You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.