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

Iyar

Which database are you using? – Radish

get total events and save to a variable. use that variable in your original select statement – Postrider

Answer 1

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

Chamorro

This works well until I put a date range in the where clause. The percentage being worked out is calculating it from the whole set of data rather than just the date range. As you can imagine this makes all the percentages really small as the event_count is lower. – Iyar

Just add the same where clause to the inner select. – Chamorro

@juergend Won' it be computationally too heavy for large datasets? – Berretta

@Ali: I don't think it is a problem with properly indexed tables. – Chamorro

@DeanFlaherty were you able to solve this? I'm trying to get the percentage to be calculated for each date total as opposed to the entire set of data. – Keelson

Answer 2

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.

Hoagy

I never saw that kind of syntax - and it solves the exact issue in a much more elegant way, since it respects whatever clauses are laid in the main WHERE. – Limit