How to get the % of a SUM colum in the same query

Question

I have a query that SUM all the amounts GROUP BY different categories. I would like to get as well the % of that SUM amount by the total.

My query is the next:

SELECT category.name, SUM(account.amount_default_currency) FROM account
INNER JOIN accounts ON account.accounts_id = accounts.id
INNER JOIN category ON account.category_id = category.id
INNER JOIN category_type ON category.category_type_id = category_type.id
GROUP BY category.name;

And I get:

name	SUM
salary	230
restaurants	2254

How could I do it?

Answer 1

Divide each sum by the total, which you can get in the same SELECT with a window function over the aggregate function:

SELECT c.name
     , sum(a.amount_default_currency) AS sum
     , round(sum(a.amount_default_currency) * 100.0
           / sum(sum(a.amount_default_currency)) OVER (), 2) AS pct
FROM   category c
JOIN   account  a ON a.category_id = c.id
GROUP  BY c.name;  -- c.id ??

This works because window functions are applied after aggregate functions. See:

• Postgres window function and group by exception

I removed joins to accounts and category_type, which are probably just ballast for the query, assuming the dropped joins wouldn't eliminate rows.

If category.id is the primary key of that table, rather use GROUP BY c.id, and include c.id in the SELECT list. ("name" is not necessarily unique?) See:

• PostgreSQL - GROUP BY clause