How to SELECT based on grouped SUM that is compared to the AVG of that grouped SUM

Question

I have a table consists of films, categories and prices. Now I want to select only categories with total price (sum of all the price per film under that category) that is higher than the average of those categories' total prices. I have been able to find the average of the total prices (thanks to this web) but can't combine it together. Here are the queries:

-- Return the average sums of the price per category
SELECT AVG(sum_price)
FROM 
(
SELECT category, sum(price) AS sum_price
FROM film_list
GROUP BY category
) AS inner_query;

-- Return category and its total price
SELECT category, SUM(price)
FROM film_list
GROUP BY category;

--[Error] Return only the category with sums of price larger than the average of sum of prices
SELECT category, SUM(price)
FROM film_list
WHERE SUM(price) >
(
   SELECT AVG(sum_price)
   FROM 
   (
       SELECT category, sum(price) AS sum_price
       FROM film_list
       GROUP BY category
   ) AS inner_query
);

Any help will be much appreciated, thanks!

Answer 1

Try adding group by and then using having

SELECT category, SUM(price)
FROM film_list
GROUP BY category
HAVING SUM(price) >
(
   SELECT AVG(sum_price)
   FROM 
   (
       SELECT category, sum(price) AS sum_price
       FROM film_list
       GROUP BY category
   ) AS inner_query
);

Answer 2

This is most easily solved using window functions:

SELECT c.*
FROM (SELECT category, SUM(price) AS sum_price,
             AVG(SUM(price)) OVER () as avg_total_price
      FROM film_list
      GROUP BY category
     ) c
WHERE sum_price > avg_total_price