AnnaB AnnaB - 11 months ago 57
SQL Question

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%


Answer Source

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,
       to_char(round(count(case when properkey = 0 then 1 end)/count(properkey), 3)*100 
                                                                   || '%' percentageids0