Calculating percentages across groups using PostgreSQL

February 10, 2023

There exists a table as below with a letter and a corresponding value.


practice=# select * from table;
 letter |  value  
--------+---------
 A      | 5000.00
 B      | 6000.00
 C      | 6000.00
 C      | 7000.00
 B      | 8000.00
 A      | 9000.00
(6 rows)

I wish to obtain the sum of each letter through use of a GROUP BY clause, and then divide the total sum for each letter by the total value of all entries in the table as a whole – 41,000 as calculated below.

practice=# select sum(value) from table;
   sum    
----------
 41000.00
(1 row)

When I run a GROUP BY clause in conjunction with a subquery, I am only able to calculate the percentage across each letter when I specify the total value of 41,000 in advance. Here is the query and output.

practice=# select letter, cast((group_values/41000)*100 as decimal(4,2)) as percentage from (select letter, sum(value) as group_values from table group by letter order by letter) as subquery;
 letter | percentage 
--------+------------
 A      |      34.15
 B      |      34.15
 C      |      31.71
(3 rows)

However, when attempting to obtain the total and then calculate the percentage, the query fails. Below is my attempt:

practice=# select letter, cast((group_values/sum(value))*100 as decimal(4,2)) as percentage from (select letter, value, sum(value) as group_values from table group by letter, value order by letter) as subquery;
ERROR:  column "subquery.letter" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select letter, cast((group_values/sum(value))*100 as decimal...

>Solution :

As select sum(value) from table returns a scalar value you can replace the calcated number with it

select 
    letter
    , cast((group_values/(select sum(value) from table))*100 as decimal(4,2)) as percentage 
from (select letter, sum(value) as group_values from table group by letter order by letter) as subquery;