Counting items per category above the avg price for that category

Question

Using Postgresql 12.2

I am working on a table with columns for meal_id (specific meal instances), type (cuisine type such as Italian, Japanese, etc.), customer ids, meal prices, etc.

I have had success using windows functions to get a list of all items per cuisine type above the average price for that type:

select m.*
  from (select m.*, avg(price) over (partition by type) as avg_price_type
     from meals m) m
  where price > avg_price_type
;

I have tried to expand this to count the meal_ids per type:

select m.*, count(meal_id)
  from (select m.*, avg(price) over (partition by type) as avg_price_type
     from meals m) m
  where price > avg_price_type
;

But I receive the error message: "ERROR: column "m.meal_id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: select m.*, count(meal_id)"

I am not sure of a workaround.

Thank you!!

Answer 1

I think you want:

select type, count(*) no_meals_over_average
from (
    select 
        m.*, 
        avg(price) over (partition by type) as avg_price_type
    from meals m
) m
where price > avg_price_type
group by type