CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Sravan
Sravan

Reputation: 193

To calculate percentage value

I have data as below..

count              ID
----------------------
10                 1
20                 2
30                 4

How can I achieve the third column which calculates the percentage in oracle.

count              ID   %
-------------------------------------
10                 1    10/(10+20+30)
20                 2    20/(10+20+30)
30                 4    30/(10+20+30)

Upvotes: 2

Views: 1645

Answers (3)

SQLMonger
SQLMonger

Reputation: 190

Window functions provides the best solution to this type of problem. What you are attempting to achieve is two levels of aggregation in one query of the table.

select id
      ,count(*)
      ,sum(count(*)) over () as CountOfAll
      ,(1.0 * count(*)) / sum(count(*)) over () as Pct
from some_table
group by id

In cases where the denominator may result in a zero, you have to wrap the Pct calculation in a CASE statement to avoid division by zero errors:

select id
  ,count(*)
  ,sum(count(*)) over () as CountOfAll
  ,case when sum(count(*)) over () = 0 
      then 0 
      else (1.0 * count(*)) / sum(count(*)) over () 
   end as Pct
from some_table
group by id

Window functions open up a lot more possibilities for creating aggregate results within a single query, and are a worthy tool to add to your SQL tool belt!

Upvotes: 0

Noel
Noel

Reputation: 10525

Use RATIO_TO_REPORT

SQL Fiddle

Query:

with your_table(count_, id_) as (
  select 10,1 from dual union all
  select 20,2 from dual union all
  select 30,4 from dual
  )
select count_, id_,
ratio_to_report(count_) over () as percentage
from your_table

Results:

| COUNT_ | ID_ |          PERCENTAGE |
|--------|-----|---------------------|
|     10 |   1 | 0.16666666666666666 |
|     20 |   2 |  0.3333333333333333 |
|     30 |   4 |                 0.5 |

Upvotes: 3

Shri Suresh
Shri Suresh

Reputation: 473

SELECT id, count, ( count / ( SELECT SUM(count) FROM table) * 100 ) as per FROM table GROUP BY id

Upvotes: 0

Related Questions