Question 89695 in PostgreSQL Question posted in
The official documentation can be found here.

Using subquery in conjunction with a WHERE clause – Postgresql

There exists the following table:

practice=# select * from table;
 letter |  value  | year 
--------+---------+------
 A      | 5000.00 | 2021
 B      | 6000.00 | 2021
 C      | 6000.00 | 2021
 B      | 8000.00 | 2022
 A      | 9000.00 | 2022
 C      | 7000.00 | 2022
 A      | 2000.00 | 2021
 B      | 1000.00 | 2022
 C      | 3000.00 | 2021
(9 rows)

In order to calculate the percentages of A, B, and C relative to the total value (i.e. the sum of A values in the table divided by the sum of all values in the table), I am using a subquery as follows:

practice=# select letter, cast((group_values/(select sum(value) from percentages)*100) as decimal(4,2)) as group_values from (select letter, sum(value) as group_values from percentages group by letter order by letter) as subquery order by group_values desc;
 letter | group_values 
--------+--------------
 A      |        34.04
 C      |        34.04
 B      |        31.91
(3 rows)

However, I now want to be able to filter the results by year, e.g. calculate the above only where the year entries are 2022, for instance.

I have tried incorporating a WHERE clause within the subquery to filter by year.

select letter, cast((group_values/(select sum(value) from percentages)*100) as decimal(4,2)) as group_values from (select letter, sum(value) as group_values from percentages where year='2022' group by letter order by letter) as subquery order by group_values desc;

However, we can see that this does not update the total to only include the entries for 2022. Instead, it seems that SQL is calculating the percentage entries for 2022 across A, B, and C for the total across all years.

 letter | group_values 
--------+--------------
 A      |        19.15
 B      |        19.15
 C      |        14.89
(3 rows)

Similarly, using the WHERE clause outside the subquery results in an error:

select letter, cast((group_values/(select sum(value) from percentages)*100) as decimal(4,2)) as group_values from (select letter, sum(value) as group_values from percentages group by letter order by letter) as subquery where year='2022' order by group_values desc;

ERROR:  column "year" does not exist
Tags:

2

Answers


  1. 0

    The subquery would get you all te years, but adding the qhere clause there will only get the numbers for 2022

    i also remove the unnecessary order by letters

    SELECT 
    letter,
    CAST((group_values / (SELECT 
                SUM(value)
            FROM
                percentages
                WHERE
    year = '2022') * 100)
        AS DECIMAL (4 , 2 )) AS group_values
FROM
    (SELECT 
        letter, SUM(value) AS group_values
    FROM
        percentages
    WHERE
        year = '2022'
    GROUP BY letter) AS subquery
ORDER BY group_values DESC;
    Login or Signup to reply.
  2. 0 
    select letter, year, sum(value) * 1 0 / sum(value) over (partition by year)
from T
where year = 2022
group by letter, year;

    The partition is redundant when all rows are in it but it will still work when the filter is removed too.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search