CODEWITHSUNDEEP
sqloracle-database
user2498741
user2498741

Reputation: 45

Oracle SQL Trying to get Percentage of columns

Hi I am using Oracle SQL Developer and am trying to get percentages on my table.

My Current Query is:

select 
  decode(grouping(life), 1, 'Total', life) as "LifeName", 
  sum(case 
        when task is not null and to_char(datee, 'MM') = '08' 
          and to_char(datee, 'YYYY') = '2013' 
        then 1 
        else 0 
      end) as "MonthStatus"
from life_lk, task_c
where life_lk.life_id = task_c.life_id
group by rollup(life)

The current output I get is:

LifeName             MonthStatus
dog                     5
bear                    20
cat                     1
Fish                    4
Total                   30

However, I want the output to be:

LifeName             MonthStatus           Percent
dog                     5                     16
bear                   20                     66
cat                     1                      3
Fish                    4                     13
Total                  30                    100

So for each cell under Month Status I want the number to be divided by the Total which in this case is 30. The number will dynamically change over time so i cannot simply divide by 30.

Sorry for the sloppy looking tables. I am not sure how to make them look neat and lined up.

Thanks for the help

Upvotes: 3

Views: 3663

Answers (4)

Scotch
Scotch

Reputation: 3226

 SELECT t1.lifename, t1.monthstatus, (t1.monthstatus / t2.total * 100) as prcent FROM
 (
 select 
   decode(grouping(life), 1, 'Total', life) as "LifeName", 
 sum(case 
    when task is not null and to_char(datee, 'MM') = '08' 
      and to_char(datee, 'YYYY') = '2013' 
    then 1 
    else 0 
  end) as "MonthStatus"
from life_lk, task_c
where life_lk.life_id = task_c.life_id
group by rollup(life) ) t1
 ,
(select sum(monthstatus) as total FROM life_lk) t2 ;

This should work, I may have got your tables and columns wrong though

Upvotes: 1

dnoeth
dnoeth

Reputation: 60472

There are two easy ways to get the right result as the ROLLUP simply adds a grand total resulting in summing every row twice:

add PARTITION BY GROUPING(life) to the ratio_to_report

or simply multiply the result times 200 instead of 100: 200*ratio_to_report(...)

Upvotes: 1

David Aldridge
David Aldridge

Reputation: 52356

The analytic function ratio_to_report() would probably work for you. It gives the ratio of a value to the sum of all of those values for a specified window.

In your case:

100* ratio_to_report(sum(...)) over ()

I'm not sure how it would work in the presence of a rollup, though.

http://docs.oracle.com/cd/E18283_01/server.112/e17118/functions142.htm

Upvotes: 3

Guga
Guga

Reputation: 37

I guess you could divide by 

SELECT COUNT (MonthStatus)

to get your porcentage.

Upvotes: 0

Related Questions