over snippets

Calculating "percentage of total" for each row with Postgres

Tagged postgres, over, window  Languages sql

Calculating the “percentage of the total” for each row with Postgres can be done with a window function:

SELECT
  *,  (value / SUM(value) OVER ()) AS "% of total"
FROM
  transactions
WHERE
  quarter = '2015-03-31' and company_id = 1;

We’re using “OVER ()”, which means the sum over all rows returned by the where clause.