SQL GROUP BY and HAVING

Question

So I don't get this error that I keep getting.

 select distinct substr(CUSTZIP, 1,5), AVG(CUSTBAL), custcity, custstate
 from customer
 group by CUSTCITY, custstate
 having CUSTSTATE = 'wa' AND avg(CUSTBAL) >100;

The error says "not a GROUP BY expression" and it suggests adding 'substr(CUSTZIP, 1,5), AVG(CUSTBAL)' to the group by clause but that doesn't work either. What I'm trying to do is list the zip codes and averages of balances by cities only in WA and have a balance more than $100. Can someone help point out my mistake to me. I'm sure its something simple but I can't seem to get it at my beginner level.

Answer 1

The problem is not the having clause. It is the substr(CUSTZIP, 1, 5). Here is one way to fix the problem:

select substr(CUSTZIP, 1, 5), AVG(CUSTBAL), custcity, custstate
from customer
group by CUSTCITY, custstate, substr(CUSTZIP, 1, 5)
having CUSTSTATE = 'wa' AND avg(CUSTBAL) > 100;

By the way, select distinct is almost never needed with group by.

Alternatively, you can use an aggregation function:

select max(substr(CUSTZIP, 1, 5)), AVG(CUSTBAL), custcity, custstate
from customer
group by CUSTCITY, custstate
having CUSTSTATE = 'wa' AND avg(CUSTBAL) > 100;