How to select province and average weight of people in those provinces only for provinces which average weight of people < 70

Enzo Austin 0
Viewed 17

A table named "people" contains the following fields:

first_name weight gender province
A 85 Male Lubusz
B 95 Female Lubusz
C 90 Female Silesia
D 95 Male Silesia
E 40 Male Opole
F 50 Female Opole

How to select the name of province and average weight of people in those provinces, only for provinces which the average weight of people is < 70.

Expected output:

province Avg_Weight
Opole 45

I have tried the following code:

SELECT province, AVG(weight) AS "Avg_Weight" 
FROM people 
WHERE "Avg_Weight" < 70 GROUP BY province;

However, the result returns the overall average weight of people in every province.

sql
1 Answers

You need to use HAVING clause & not WHERE clause as fiter is to be done on aggregated column.

select 
  "province", 
  avg("weight") as avg_weight 
from 
  people 
group by 
  "province" 
having 
  avg("weight") < 70;

Demo

useri1kr0
Related