I have a products table with following values with id as INT and profit as numeric data type

id profit
1   6.00
2   3.00
3   2.00
4   3.00
5   2.00
6   8.00
7   4.00
8   3.00
9   1.00
10  4.00
11  10.00
12  3.00
13  6.00
14  5.00
15  2.00
16  7.00
17  6.00
18  5.00
19  2.00
20  16.00
21  3.00
22  6.00
23  5.00
24  5.00
25  1.00
26  4.00
27  1.00
28  7.00
29  11.00
30  2.00
31  1.00
32  3.00
33  2.00
34  5.00
35  4.00

I want to fetch id's which have profit more than average profit

My QUERY:

SELECT product_id,profit
FROM products
GROUP BY product_id,profit
HAVING profit > AVG(profit)::INT 

But, the above query return's empty result.

CodePudding user response：

when you execute the group by query, the records are grouped based on the parameters and then where/having clauses are applied.

so first group is student id 1 and further grouped by profit 6.00 making its average as 6.00 and with having condition profit >avg(profit), there are no records which match the criteria. same for all other records. that is why you get empty result set as no number can be > itself.

based on your description though, it can be achieved by multiple selects.

select * from products where profit >(select avg(profit) from products)