Home » SQL & PL/SQL » SQL & PL/SQL » Query with horizontal running totals
Query with horizontal running totals [message #573095] Thu, 20 December 2012 13:50 Go to next message
sql_dvlpr
Messages: 1
Registered: December 2012
Junior Member
Hi:
I'm trying to create a report in the following format
Year Name Reg1 Reg2 Reg3
2001 Al      3    4    5
2001 Le      4    1    1
2001         7    5    6
2002 Sue     2    4    1
2002 Al      1    3    6
2002 Jim     6    1    3
2002        16   15   16
2003 Jim     4   -3    2
2003 Le     -2    4    5
2003        20   16   23 

Note that the totals are accumulating horizontally, broken on year. How do I do that, please? Thanks very much!
Al

[EDITED by LF: applied [code] tags]

[Updated on: Thu, 20 December 2012 14:30] by Moderator

Report message to a moderator

Re: Query with horizontal running totals [message #573097 is a reply to message #573095] Thu, 20 December 2012 14:00 Go to previous messageGo to next message
Michel Cadot
Messages: 59087
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

With any SQL or PL/SQL question, please, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: Query with horizontal running totals [message #573106 is a reply to message #573097] Thu, 20 December 2012 19:58 Go to previous messageGo to next message
Flyby
Messages: 144
Registered: March 2011
Location: Belgium
Senior Member
Looks like the group by (partial) rollup clause in the select statement

[Updated on: Thu, 20 December 2012 19:59]

Report message to a moderator

Re: Query with horizontal running totals [message #573143 is a reply to message #573095] Fri, 21 December 2012 13:31 Go to previous message
Solomon Yakobson
Messages: 2028
Registered: January 2010
Senior Member
sql_dvlpr wrote on Thu, 20 December 2012 14:50
Hi:
Note that the totals are accumulating horizontally, broken on year.


Well, I am assuming Reg1 = 20 for 2003 total is a typo and it should be 18. Then:

with t as (
           select 2001 year,'Al' name,3 reg1,4 reg2,5 reg3 from dual union all
           select 2001,'Le',4,1,1 from dual union all
           select 2002,'Sue',2,4,1 from dual union all
           select 2002,'Al',1,3,6 from dual union all
           select 2002,'Jim',6,1,3 from dual union all
           select 2003,'Jim',4,-3,2 from dual union all
           select 2003,'Le',-2,4,5 from dual
          )
select  year,
        name,
        nvl2(name,sum(reg1),sum(sum(reg1)) over(partition by name order by year)) reg1,
        nvl2(name,sum(reg2),sum(sum(reg2)) over(partition by name order by year)) reg2,
        nvl2(name,sum(reg3),sum(sum(reg3)) over(partition by name order by year)) reg3
  from  t
  group by grouping sets((year),(year,name))
  order by year,
           name nulls last
/

      YEAR NAM       REG1       REG2       REG3
---------- --- ---------- ---------- ----------
      2001 Al           3          4          5
      2001 Le           4          1          1
      2001              7          5          6
      2002 Al           1          3          6
      2002 Jim          6          1          3
      2002 Sue          2          4          1
      2002             16         13         16
      2003 Jim          4         -3          2
      2003 Le          -2          4          5
      2003             18         14         23

10 rows selected.

SQL> 


SY.
P.S. Next time provide SQL to create table and populate it with sample date. I'll not be that generous to type it in myself.
Previous Topic: Outer Joins & Not Null check
Next Topic: Oracle database to Mysql Conversion
Goto Forum:
  


Current Time: Tue Sep 16 15:10:41 CDT 2014

Total time taken to generate the page: 1.93060 seconds