I have a table car with fields model, engine, sales, type, where type can only be one of 4: sedan,coupe,sports,hatchback.

I need to find which car models have sold over double the average of all the other cars of this type. I know how to find an average for all cars of each type, but I cannot work out how to find the average for each type excluding the current row.

My idea was something like: select model, sales from car group by type having sales > 2*avg(average sales for this type not including the sales of one car)

I have also tried to add case when into the average calculation but I could not manage to make it work.

CodePudding user response：

You can use a window function with a framing clause and a frame exclusion. You need two levels of query, as windows functions cannot be in WHERE or HAVING of their same level:

select * from (
    select 
       *,
       avg(sales) over (partition by type rows between unbounded preceding and unbounded following exclude current row) as avg_other from car
) as foo
where sales>2*avg_other