SQL - Calculate percentage on count(column)

Question

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

Answer 1

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.

Answer 2

If your DB engines 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