12 Replies Latest reply: Oct 16, 2012 1:48 AM by user555994 RSS

    Issue with how to compute additional column in query output.

    user555994
      Hello PL/SQL Gurus/experts,

      I am using Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production version


      Currently i have the data in the following way -
      drop table T2;
      create table T2(Year, MastTot, BechTot, Tot) as select
      '2008', '20000', '450000', '470000' from dual union all select
      '2008', '50000', '324000', '374000'  from DUAL union all select
      '2009', '25000', '450000', '475000'  from dual union all select
      '2009', '250000', '324000', '574000' from DUAL union all select
      '2010', '120000', '450000', '570000' from dual union all select
      '2010', '52000', '324000', '376000'  from DUAL union all select
      '2011', '220000', '450000', '670000' from dual union all select
      '2011', '52000', '324000', '376000'  from DUAL ;
      I want the data to be presented in the following way (output)-
      Year     MastTot     %Change     BechTot     %Change     Tot     %Change     %Total
      2009     275000     292.86     774000     0     1049000     24.29     27
      2010     172000     -37.45     774000     0     946000     -9.82     24.35
      2011     272000     58.14     774000     0     1046000     10.57     26.92
      Total     789000     313.54     3096000     0     3885000     25.04     78.28
      I am using the following sql query -
      select decode(grouping(Year), 0, Year, 'Total') Year,
             To_Char(sum(MastTot), '999,999,999,999,999,999,999.99')  "MastTot",
             To_Char(sum(BechTot), '999,999,999,999,999,999,999.99')  "BechTot",
             To_Char(sum(Tot), '999,999,999,999,999,999,999.99')  "Tot",
             round((sum(Tot)/nullif(max(total_amount),0)) * 100, 2) "%Total"
        From
      (
      select Year,
             sum(MastTot) MastTot,
             sum(BechTot) BechTot,
             sum(Tot) Tot,
             SUM(Tot) over() total_amount
        From t2
      group by Year,Tot
      )
      where Year>'2008'
       group by grouping sets((Year),())
      order by Year
      But i am not able to fetch the %Change column. Kindly help me with that.
      All efforts and help in this regard is highly appericated and i will be thankful to you ...
        • 1. Re: Issue with how to compute additional column in query output.
          jeneesh
          SQL> with t
            2  as (
            3  select year,masttot,lag(masttot) over(order by year) prevm,
            4      BechTot,lag(BechTot) over(order by year) prevb,
            5      tot,lag(Tot) over(order by year) prevt,
            6      sum(tot) over(order by null) sm_t
            7  from(
            8  select Year,
            9         sum(MastTot) MastTot,
           10         sum(BechTot) BechTot,
           11         sum(Tot) Tot
           12  From t2
           13  group by Year
           14   ))
           15  select year,sum(masttot) masttot,
           16     sum(((masttot-prevm)/prevm)*100) Perc_m,
           17         sum(BechTot) bechtot,
           18         sum(((BechTot-prevb)/prevb)*100) Perc_b,
           19         sum(tot) tot,sum(((tot-prevt)/prevt)*100) Perc_t,
           20         sum((tot/sm_t)*100)  perc_total
           21  from t
           22  where year > 2008
           23  group by rollup(year);
          
                YEAR    MASTTOT     PERC_M    BECHTOT     PERC_B        TOT     PERC_T PERC_TOTAL
          ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                2009     275000 292.857143     774000          0    1049000 24.2890995  27.001287
                2010     172000 -37.454545     774000          0     946000 -9.8188751 24.3500644
                2011     272000 58.1395349     774000          0    1046000 10.5708245 26.9240669
                         719000 313.542132    2322000          0    3041000 25.0410489 78.2754183
          Edited by: jeneesh on Oct 10, 2012 7:09 PM
          • 2. Re: Issue with how to compute additional column in query output.
            user555994
            Thanks for the reply jeneesh,

            are you reffering to the line in the code
            round((sum(Tot)/nullif(max(total_amount),0)) * 100, 2) "%Total"
            Here i am looking for the %Total against the maximum value of the tot ......
            • 3. Re: Issue with how to compute additional column in query output.
              Purvesh K
              This way!!!
              select year, sum(masttot), sum(change), sum(bechtot), sum(b_change), sum(tot), sum(t_change)
                from (
                        select lead(year) over(order by year) year, 
                               lead(masttot) over (order by year) masttot,
                               round((lead(masttot) over (order by year) - masttot)/masttot, 4) * 100 Change,
                               lead(bechtot) over (order by year) bechtot,
                               round((lead(bechtot) over (order by year) - bechtot)/bechtot, 4) * 100 B_Change,
                               lead(tot) over (order by year) tot,
                               round((lead(tot) over (order by year) - tot)/tot, 4) T_Change
                          from (
                                select year, sum(masttot) masttot, sum(bechtot) bechtot, sum(tot) tot
                                  from t2
                                 group by year
                                 order by year
                               )
                     ) a 
               where year is not null
               group by year
              
              YEAR                   SUM(MASTTOT)           SUM(CHANGE)            SUM(BECHTOT)           SUM(B_CHANGE)          SUM(TOT)               SUM(T_CHANGE)          
              ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- ---------------------- 
              2009                   275000                 292.86                 774000                 0                      1049000                0.2429                 
              2010                   172000                 -37.45                 774000                 0                      946000                 -0.0982                
              2011                   272000                 58.14                  774000                 0                      1046000                0.1057
              • 4. Re: Issue with how to compute additional column in query output.
                jeneesh
                user555994 wrote:
                Thanks for the reply jeneesh,

                are you reffering to the line in the code
                round((sum(Tot)/nullif(max(total_amount),0)) * 100, 2) "%Total"
                Here i am looking for the %Total against the maximum value of the tot ......
                SQL> with t
                  2  as (
                  3  select year,masttot,lag(masttot) over(order by year) prevm,
                  4      BechTot,lag(BechTot) over(order by year) prevb,
                  5      tot,lag(Tot) over(order by year) prevt,
                  6      sum(tot) over(order by null) sm_t
                  7  from(
                  8  select Year,
                  9         sum(MastTot) MastTot,
                 10         sum(BechTot) BechTot,
                 11         sum(Tot) Tot
                 12  From t2
                 13  group by Year
                 14   ))
                 15  select year,sum(masttot) masttot,
                 16     sum(((masttot-prevm)/prevm)*100) Perc_m,
                 17         sum(BechTot) bechtot,
                 18         sum(((BechTot-prevb)/prevb)*100) Perc_b,
                 19         sum(tot) tot,sum(((tot-prevt)/prevt)*100) Perc_t,
                 20         sum((tot/sm_t)*100)  perc_total
                 21  from t
                 22  where year > 2008
                 23  group by rollup(year);
                
                      YEAR    MASTTOT     PERC_M    BECHTOT     PERC_B        TOT     PERC_T PERC_TOTAL
                ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
                      2009     275000 292.857143     774000          0    1049000 24.2890995  27.001287
                      2010     172000 -37.454545     774000          0     946000 -9.8188751 24.3500644
                      2011     272000 58.1395349     774000          0    1046000 10.5708245 26.9240669
                               719000 313.542132    2322000          0    3041000 25.0410489 78.2754183
                • 5. Re: Issue with how to compute additional column in query output.
                  chris227
                  with t2 (Year, MastTot, BechTot, Tot) as (
                  select
                  '2008', '20000', '450000', '470000' from dual union all select
                  '2008', '50000', '324000', '374000'  from DUAL union all select
                  '2009', '25000', '450000', '475000'  from dual union all select
                  '2009', '250000', '324000', '574000' from DUAL union all select
                  '2010', '120000', '450000', '570000' from dual union all select
                  '2010', '52000', '324000', '376000'  from DUAL union all select
                  '2011', '220000', '450000', '670000' from dual union all select
                  '2011', '52000', '324000', '376000'  from DUAL
                  )
                  
                  select 
                   year
                  ,sum(MastTot) MastTot
                  ,round(sum(c_MastTot),2) c_MastTot
                  ,sum(BechTot) BechTot
                  ,round(sum(c_BechTot),2) c_BechTot
                  ,sum(Tot) Tot
                  ,round(sum(c_Tot),2) c_Tot
                  ,round(sum(c_total_total),2) c_total_total
                   from (
                  select year,
                         sum(MastTot) MastTot
                        ,(sum(MastTot)-lag(sum(MastTot)) over (order by year))
                         /lag(sum(MastTot)) over (order by year)*100 c_MastTot
                        ,sum(BechTot) BechTot
                        ,(sum(BechTot)-lag(sum(BechTot)) over (order by year))
                         /lag(sum(BechTot)) over (order by year)*100 c_BechTot
                        ,sum(Tot) Tot
                        ,(sum(Tot)-lag(sum(Tot)) over (order by year))
                         /lag(sum(Tot)) over (order by year)*100 c_Tot
                        ,sum(Tot)*100/sum(sum(tot)) over () c_total_total
                    from t2
                  group by year
                  )
                  where year > '2008'
                  group by rollup (year)
                  
                  YEAR MASTTOT C_MASTTOT BECHTOT C_BECHTOT TOT C_TOT C_TOTAL_TOTAL 
                  2009 275000 292.86 774000 0 1049000 24.29 27 
                  2010 172000 -37.45 774000 0 946000 -9.82 24.35 
                  2011 272000 58.14 774000 0 1046000 10.57 26.92 
                  -  719000 313.54 2322000 0 3041000 25.04 78.28 
                  • 6. Re: Issue with how to compute additional column in query output.
                    Frank Kulash
                    Hi,

                    Here's one way:
                    SELECT  DECODE ( GROUPING (year)
                                , 0       , year
                                             , 'Total'
                                )          AS year
                    ,       TO_CHAR (SUM (MastTot), '999,999,999,999,999,999,999.99')     AS "MastTot"
                    ,     TO_CHAR     (SUM (pct_change),                  '999.99')     AS "%Change"
                    ,       TO_CHAR (SUM (BechTot), '999,999,999,999,999,999,999.99')       AS "BechTot"
                    ,       TO_CHAR (SUM (Tot),      '999,999,999,999,999,999,999.99')       AS "Tot"
                    ,       ROUND ( ( SUM (Tot)
                                    / NULLIF ( MAX (total_amount)
                                      , 0
                                    )
                              ) * 100
                               , 2
                               )                                        AS "%Total"
                    FROM    (
                             SELECT    Year
                             ,         SUM (MastTot)     AS MastTot
                             ,           SUM (BechTot)      AS BechTot
                             ,         SUM (Tot)      AS Tot
                             ,         SUM (SUM (Tot)) OVER () AS total_amount
                             ,           ( SUM (MastTot) * 100
                                                          / NULLIF ( LAG (SUM (MastTot)) OVER (ORDER BY  Year)
                                                                           , 0
                                                           )
                                    - 
                    100
                                    )          AS pct_change
                               FROM      t2
                             GROUP BY  Year
                         )
                    WHERE        Year     > '2008'
                    GROUP BY  GROUPING SETS ( (Year)
                                              , ()
                                   )
                    ORDER BY  Year
                    ;
                    There's no reason to GROUP BY Tot in the sub-query.
                    • 7. Re: Issue with how to compute additional column in query output.
                      user555994
                      Thanks a lot to Purvesh K , jeneesh ,chris227 and Frank Kulash
                      U Guys are just amazing and only i can say is Vov. This has definately helped me and i am really thankful to you guys.

                      Thanks again
                      • 8. Re: Issue with how to compute additional column in query output.
                        user555994
                        Hello Purvesh K/jeneesh

                        If data turn out to the be following -
                        drop table T2;
                        create table T2(Year, MastTot, BechTot, Tot) as select
                        '2008', '20000', '450000', '470000' from dual union all select
                        '2008', '50000', '324000', '374000'  from DUAL union all select
                        '2009', '25000', '450000', '475000'  from dual union all select
                        '2009', '250000', '324000', '574000' from DUAL union all select
                        '2010', '120000', '0', '570000' from dual union all select
                        '2010', '52000', '0', '376000'  from DUAL union all select
                        '2011', '220000', '450000', '670000' from dual union all select
                        '2011', '52000', '324000', '376000'  from DUAL ;
                        and if i use the following sql -
                        select year, sum(masttot), sum(change), sum(bechtot), sum(b_change), sum(tot), sum(t_change)
                          from (
                                  select lead(year) over(order by year) year, 
                                         lead(masttot) over (order by year) masttot,
                                         round((lead(masttot) over (order by year) - masttot)/nullif(masttot,0), 4) * 100 Change,
                                         lead(bechtot) over (order by year) bechtot,
                                         round((lead(bechtot) over (order by year) - bechtot)/nullif(bechtot,0), 4) * 100 B_Change,
                                         lead(tot) over (order by year) tot,
                                         round((lead(tot) over (order by year) - tot)/nullif(tot,0), 4) T_Change
                                    from (
                                          select year, sum(masttot) masttot, sum(bechtot) bechtot, sum(tot) tot
                                            from t2
                                           group by year
                                           order by year
                                         )
                               ) a 
                         where year is not null
                         group by year
                        or
                        with t
                        as (
                        select year,masttot,lag(masttot) over(order by year) prevm,
                         BechTot,lag(BechTot) over(order by year) prevb,
                        tot,lag(Tot) over(order by year) prevt,
                        sum(tot) over(order by null) sm_t
                        from(
                        select Year,
                        sum(MastTot) MastTot,
                        sum(BechTot) BechTot,
                        sum(Tot) Tot
                        From t2
                        group by Year
                        ))
                        select year,sum(masttot) masttot,
                        sum(((masttot-prevm)/nullif(prevm,0))*100) Perc_m,
                        sum(BechTot) bechtot,
                        sum(((BechTot-prevb)/nullif(prevb,0))*100) Perc_b,
                        sum(tot) tot,sum(((tot-prevt)/nullif(prevt,0))*100) Perc_t,
                        sum((tot/nullif(sm_t,0))*100)  perc_total
                        from t
                        where year > 2008
                        group by rollup(year);
                        then it is not giving the correct result as in case of 2010 to 2011 in %B (Bechelor) total there is 100% change as from in year 2010 sum(bechtot)=0 and in year 2011 it is 774000.
                        Whats wrong with this ..........

                        Thanks in advance for your help/effort and valuable

                        Edited by: user555994 on Oct 15, 2012 5:24 AM
                        • 9. Re: Issue with how to compute additional column in query output.
                          jeneesh
                          user555994 wrote:
                          Hello Purvesh K/jeneesh

                          If data turn out to the be following -

                          then it is not giving the correct result as in case of 2010 to 2011 in %B (Bechelor) total there is 100% change as from in year 2010 sum(bechtot)=0 and in year 2011 it is 774000.
                          Whats wrong with this ..........
                          Change from 0 to some value (for example 774000) is not 100% change.

                          You cannot calculate the percentage change in that case (Or the change is infinite - represenetd by NULL here). if values is changing form 10 to 20, it is 100% change.

                          And in case of NULL, if you want to display 100% you can do that something like below, but mathematically it is not correct.
                          with t
                          as (
                          select year,masttot,lag(masttot) over(order by year) prevm,
                                  BechTot,lag(BechTot) over(order by year) prevb,
                                  tot,lag(Tot) over(order by year) prevt,
                                  sum(tot) over(order by null) sm_t
                          from(
                              select Year,
                                  sum(MastTot) MastTot,
                                  sum(BechTot) BechTot,
                                  sum(Tot) Tot
                              From t2
                              group by Year
                          ))
                          select year,sum(masttot) masttot,
                                  sum(((masttot-prevm)/decode(prevm,0,masttot,prevm))*100) Perc_m,
                                  sum(BechTot) bechtot,
                                  sum(((BechTot-prevb)/decode(prevb,0,bechtot,prevb))*100) Perc_b,
                                  sum(tot) tot,sum(((tot-prevt)/decode(prevt,0,tot,prevt))*100) Perc_t,
                                  sum((tot/nullif(sm_t,0))*100)  perc_total
                          from t
                          where year > 2008
                          group by rollup(year);
                          
                          YEAR MASTTOT PERC_M BECHTOT PERC_B TOT PERC_T PERC_TOTAL
                          ---- ------- ------ ------- ------ --- ------ ----------
                          2009  275000 292.8571429  774000      0 1049000 24.28909953  27.001287 
                          2010  172000 -37.45454545       0   -100 946000 -9.818875119 24.35006435 
                          2011  272000 58.13953488  774000    100 1046000 10.57082452 26.92406692 
                                719000 313.5421323 1548000      0 3041000 25.04104893 78.27541828 
                          Edited by: jeneesh on Oct 15, 2012 6:16 PM
                          • 10. Re: Issue with how to compute additional column in query output.
                            user555994
                            Jeneesh thanks for such a prompt reply.

                            Agree to your point -

                            Change from 0 to some value (for example 774000) is not 100% change.

                            You cannot calculate the change (Or it is infinity - represenetd by NULL here). if values is changing form 10 to 20, it is 100% change.
                            And in case of NULL, if you want to display 100% you can do that something like below, but mathematically it is not correct.

                            But in case 0 to some value (for example 774000) i want to display it as 774000% and surely not the 100%.
                            • 11. Re: Issue with how to compute additional column in query output.
                              jeneesh
                              user555994 wrote:

                              But in case 0 to some value (for example 774000) i want to display it as 774000% and surely not the 100%.
                              Ok, play with decode
                              with t
                              as (
                              select year,masttot,lag(masttot) over(order by year) prevm,
                                      BechTot,lag(BechTot) over(order by year) prevb,
                                      tot,lag(Tot) over(order by year) prevt,
                                      sum(tot) over(order by null) sm_t
                              from(
                                  select Year,
                                      sum(MastTot) MastTot,
                                      sum(BechTot) BechTot,
                                      sum(Tot) Tot
                                  From t2
                                  group by Year
                              ))
                              select year,sum(masttot) masttot,
                                      sum(((masttot-prevm)/decode(prevm,0,100,prevm))*100) Perc_m,
                                      sum(BechTot) bechtot,
                                      sum(((BechTot-prevb)/decode(prevb,0,100,prevb))*100) Perc_b,
                                      sum(tot) tot,sum(((tot-prevt)/decode(prevt,0,100,prevt))*100) Perc_t,
                                      sum((tot/decode(sm_t,0,100,sm_t))*100)  perc_total
                              from t
                              where year > 2008
                              group by rollup(year);
                              
                              YEAR MASTTOT PERC_M BECHTOT PERC_B TOT PERC_T PERC_TOTAL
                              ---- ------- ------ ------- ------ --- ------ ----------
                              2009  275000 292.8571429  774000      0 1049000 24.28909953  27.001287 
                              2010  172000 -37.45454545       0   -100 946000 -9.818875119 24.35006435 
                              2011  272000 58.13953488  774000 774000 1046000 10.57082452 26.92406692 
                                    719000 313.5421323 1548000 773900 3041000 25.04104893 78.27541828 
                              • 12. Re: Issue with how to compute additional column in query output.
                                user555994
                                Thanks a lot jeneesh

                                you are simply amazing. vov i am really thankful to you sir.