Today I Learned

A Hashrocket project

Compute group proportions of a whole in SQL

It's easy with SQL to use aggregates to count members of a group. But what if you also want the proportion that group is of the whole. The answer is to use a window function combined with the aggregate. The window function is evaluated after the normal aggregation. i.e. it can reference the values of other post-aggregation rows.

select type, count(*), count(*)::numeric / sum(count(*)) over ()
from pets
group by type;
  type  | count |        ?column?        
--------+-------+------------------------
 dog    |    13 | 0.52000000000000000000
 rabbit |     3 | 0.12000000000000000000
 cat    |     9 | 0.36000000000000000000

Looking for help? Hashrocket developers believe that data quality is as important as code quality. We enjoy all the challenges of relational databases, from finding the fastest index, to structuring data to fit the needs of an application. We're eager to share our experiences; check out PG Casts, a series of free weekly PostgreSQL screencasts.