CODEWITHSUNDEEP
sqlpercentagecalculated-field
Dean Flaherty
Dean Flaherty

Reputation: 351

SQL - Calculate percentage on count(column)

I have the following piece of code which counts how many times something appears in the event column. 

SELECT event, count(event) as event_count   
FROM event_information
group by event

event   event_count
a       34
b       256
c       45
d       117
e       3

I want to be able to calculate the percentage of each of the rows like so.

event   event_count event_percent
a       34          7.47
b       256         56.26
c       45          9.89
d       117         25.71
e       3           0.66

Upvotes: 29

Views: 122119

Answers (2)

juergen d
juergen d

Reputation: 204746

If your DB engine does not support Window functions then you can use a subquery:

SELECT event, 
       count(event) as event_count,
       count(event) * 100.0 / (select count(*) from event_information) as event_percent
FROM event_information
group by event

Upvotes: 27

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Most SQL dialects support ANSI standard window functions. So, you can write the query as:

select event, count(*) as event_count,
       count(*) * 100.0/ sum(count(*)) over () as event_percent
from event_information
group by event;

Window functions are generally more efficient than subqueries and other methods.

Upvotes: 46

Related Questions