1. SQL Recipes
2. Calculations Per Group
3. Calculating Percentage (%) of Total Sum

Calculating Percentage (%) of Total Sum in SQL

How to compute the ratio of a particular row to the sum of values?

This question comes up frequently when you want to the relative contribution of a row against the backdrop of the total sum of all the rows. For example:

• how is the browser marketshare changing over time
• what's each sales person's contribution to your company's revenue

Consider a table with the number of page view (in billions) with each browser:

What we really want to see is the browser market share. We can use a Common Table Expression (CTE) to compute the total pageview which we then use to compute the ratio:

with total as
    ( select sum(pageviews) as total
    from pageviews )
select browser,
    pageviews / total.total as share
from pageviews,
    total

Which gives a ratio of each browser to the total:

And the visualization:

Percentage to Total per Group

The next question to ask is how this is changing over time?

What we are attempting to do here is to group our data into months, compute the total for that group and for each row within that group compute a ratio. An overall total wouldn't make sense. Conside the pageview table as before, but with an additional date field:

We once again to resort to window functions with a partition over the month portion of the datetime.

select date_trunc('month', dt),
    browser,
    pageviews / sum(pageviews) over(partition by date_trunc('month', dt))
from pageviews 

Let's unpack the query a bit. Our window function creates partitions (groups) for each month, and for each partition we sum up the pageviews. The ratio between the current row pageviews and the total for that group is exactly what we want.

Redshift has ratio_to_report

Fortunately on Redshift, it supports a window function called ratio_to_report which computes the ratio of the value of a column in a row with that of the sum of the said expression over the group.

select date_trunc('month', dt),
    browser,
    ratio_to_report(pageviews) over(partition by date_trunc('month', dt))
from pageviews