Query for Running Totals with respect to dept's

• Oracle_91

  SSChampion

  Points: 14548

  More actions

  June 16, 2009 at 9:16 pm

  #135348

  Hi Friends,

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

  Any help would be 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 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.

• Adrienne250

  Right there with Babe

  Points: 737

  More actions

  June 17, 2009 at 8:48 am

  #1011009

  Can you just add this...

  ,(select sum(b.sal) from cte b where b.seq_no <= a.seq_no and b.deptno = a.deptno) as "RunningTotalWRTDept"

• Oracle_91

  SSChampion

  Points: 14548

  More actions

  June 17, 2009 at 12:24 pm

  #1011195

  Thank You!

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  June 26, 2009 at 10:57 am

  #1016191

  Here is another option that won't use a triangular join and scales quite well:

  CREATE TABLE dbo.EMPP

  (EMPNO INT,

  ENAME VARCHAR(20),

  SAL INT,

  DEPTNO INT

  )

  INSERT INTO dbo.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;

  create table #empp (

  empno int,

  ename varchar(20),

  sal int,

  deptno int,

  deptbal int null,

  unique clustered(

  deptno,

  empno

  ));

  insert into #empp(empno, ename, sal, deptno)

  select EMPNO, ENAME, SAL, DEPTNO

  from dbo.EMPP;

  declare @empno int,

  @deptno int,

  @deptbal int;

  set @deptno = -1;

  update #empp set

  @deptbal = deptbal = case when emp.deptno @deptno then 0 else @deptbal end + sal,

  @deptno = emp.deptno

  from

  #empp emp with (index(0));

  select

  empno,

  ename,

  sal,

  deptno,

  deptbal

  from

  #empp

  order by

  deptno,

  empno;

  drop table #empp;

  drop table dbo.EMPP;

• Jeff Moden

  SSC Guru

  Points: 1004366

  More actions

  June 26, 2009 at 1:43 pm

  #1016263

  Adrienne (6/17/2009)

  ---

  Can you just add this...

  ,(select sum(b.sal) from cte b where b.seq_no <= a.seq_no and b.deptno = a.deptno) as "RunningTotalWRTDept"

  Ummmm.... no. While that works, it has no scalability and performance will absolutely die in the face of any decent row count. It's a triangular join and even with small rowcounts, it just works the hell out of the server.

  Please see the following article why you should never use an inequality with an aggregate in this fashion.

  http://www.sqlservercentral.com/articles/T-SQL/61539/

  Lynn posted a correct answer. Just make sure that you have the clustered index that he put on his table. The update will not work unless that index is in the correct order AND it's a clustered index.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)