29

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
sgeddes
  • 62,311
  • 6
  • 61
  • 83
Dean Flaherty
  • 351
  • 1
  • 5
  • 15

2 Answers2

44

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 1
    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. – igorsantos07 Mar 05 '21 at 02:42
27

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
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 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. – Dean Flaherty May 19 '16 at 09:00
  • Just add the same `where` clause to the inner select. – juergen d May 19 '16 at 10:08
  • @juergend Won' it be computationally too heavy for large datasets? – Ali Mar 23 '19 at 23:31
  • @Ali: I don't think it is a problem with properly indexed tables. – juergen d Mar 24 '19 at 05:46
  • @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. – cheena Jun 30 '22 at 20:26