SQL code for taking only products with avg value higher then avg global price for all products-CodePudding

Trying to find average price for category and compare it with total avg value and group only products/category where product avg price > total avg value.

CREATE TABLE a (t int, q int);
    
INSERT INTO a (t, q)
VALUES
    (1, 6),
    (1, 6),
    (1, 4),
    (1, 8),
    (2, 6),
    (2, 4),
    (2, 1),
    (2, 1)
;

My code:

 select a.t, b.AvgQ as avg_group
 from a join 
 (select AVG(q) as AvgQ, t from a group by t) b on a.t=b.t
 where avg(a.q) < b.AvgQ
 group by a.t

I was trying compare a.q (as average price for all products) with b.AvgQ as average price for one type of products. However I can not use WHERE avg(a.q).

As solution I have to get table as:

t | avg_group
-- ----------
1 | 6

CodePudding user response：

where product avg price > total avg value.

Simply:

SELECT t, avg(q) AS avg_group
FROM   a
GROUP  BY t
HAVING avg(q) > (SELECT avg(q) FROM a);

Produces your desired result.

CodePudding user response：

You can't use the Aggregrate functions (MAX, MIN, SUM, AVG etc) in the WHERE condition. You can use HAVING or in your case a subquery works perfectly:

select a.t, b.AvgQ as avg_group
from a 
inner join 
      ( select AVG(q) as AvgQ, t 
        from a 
        group by t) as b on a.t=b.t
 where (select avg(q) from a ) < b.AvgQ
 group by a.t ;

Demo: https://www.db-fiddle.com/f/7yUJcuMJPncBBnrExKbzYz/138