Using subquery in conjunction with a WHERE clause

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

>Solution :

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;

Leave a Reply