This discussion is archived
12 Replies Latest reply: Oct 15, 2012 11:48 PM by user555994 RSS

Issue with how to compute additional column in query output.

user555994 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks a lot jeneesh

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points