CODEWITHSUNDEEP
sqloracle-database
AnnaB
AnnaB

Reputation: 57

How to calculate percentage in oracle sql

I have a table in which I have multiple IDs which can have a value or 0. The IDs come from different sources so I would like to know what is the percentage of IDs with the value 0 as a percentage of total IDs, for each source file.

Sample Data:

ID  Source    
1    aaa     
0    aaa     
2    bbb     
0    ccc     
3    ccc     
0    ccc 
5    aaa 
0    bbb
6    bbb 
7    bbb

I need to display Output like:

CountOfIDs0   TotalIDs Source  PercentageIDs0
2               3         ccc     66.6%%
1               3         aaa     33.3%%
1               4         bbb     25%

Thanks!

Upvotes: 1

Views: 55949

Answers (6)

v83rahul
v83rahul

Reputation: 313

I calculated percentage of values in a column by using below query

Select A.,B., to_char((A.count_service/B.count_total)*100)||'%' from (Select type_cd, count(type_cd) as count_type from table1 group by type_cd) A cross join (Select count(type_cd) as count_total from table1) B ;

Upvotes: 0

Karapet
Karapet

Reputation: 1

select Source, ROUND(100*number/sum(number) OVER (PARTITION BY p),2) as percentage, sum(number) OVER (PARTITION BY p) as total from( select 1 p, Source , count(Source) number from declaration_assessment_result GROUP by Source )x

Upvotes: -1

Tharunkumar Reddy
Tharunkumar Reddy

Reputation: 2813

Apply round function.

 select count(id) as TotalIDs ,Source, sum(case when id=0 then 1 end) countid0,
        to_char((sum(case when id=0 then 1 end)/count(id))*100)||'%' as PercentageIDs0
     from Table1 group by Source

Upvotes: 2

user5683823
user5683823

Reputation:

If you want a result like 66.6% rather than 66.7%, you would use trunc() rather than round() (although the latter is probably better). And you need to round a/b to three decimal places, so there is one left after you multiply by 100.

Then, you can have both counts in one query, and you can add the percentage calculation also in the same query.

select count(case when propkey = 0 then 1 end) countid0,
       count(propkey) totalidcount,
       source,
       to_char(round(count(case when properkey = 0 then 1 end)/count(properkey), 3)*100) 
                                                                   || '%' percentageids0
from......

Upvotes: 5

am2
am2

Reputation: 371

I would do it that way:

With MyRows AS (
  SELECT 1 ID, 'aaa' SOURCE FROM DUAL UNION ALL
  SELECT 0, 'aaa' FROM DUAL UNION ALL
  SELECT 2, 'bbb' FROM DUAL UNION ALL
  SELECT 0, 'ccc' FROM DUAL UNION ALL
  SELECT 3, 'ccc' FROM DUAL UNION ALL
  SELECT 0, 'ccc' FROM DUAL UNION ALL
  SELECT 5, 'aaa' FROM DUAL UNION ALL
  SELECT 0, 'bbb' FROM DUAL UNION ALL
  SELECT 6, 'bbb' FROM DUAL UNION ALL
  SELECT 7, 'bbb' FROM DUAL 
)
SELECT 
  DISTINCT SOURCE,
  SUM(CASE WHEN ID = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY SOURCE) ZERO_IDS,
  COUNT(ID) OVER (PARTITION BY SOURCE) TOTAL_IDS,
  (100 * SUM(CASE WHEN ID = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY SOURCE))/(COUNT(ID) OVER (PARTITION BY SOURCE)) PERCENTAGE
FROM MyRows
;

Upvotes: 0

rani
rani

Reputation: 68

For Unique record you have to use DISTINCT Query

Upvotes: 0

Related Questions