CODEWITHSUNDEEP
sqlgroup-byhaving-clause
Restnom
Restnom

Reputation: 134

SQL GROUP BY and HAVING

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.

Upvotes: 3

Views: 170

Answers (3)

Tangoo
Tangoo

Reputation: 1439

Here's some advice:

  1. You should take the suggestion that adding substr(CUSTZIP, 1,5), AVG(CUSTBAL)' to the group by clause.

  2. Pay much attention the distinct and group by, having usage.

Upvotes: 0

sgeddes
sgeddes

Reputation: 62841

The error you are getting is because you are trying to perform an aggregation (i.e. sum, avg, etc.) and not including all of the columns in your select clause to your group by clause. If you are selecting the column, then you should be grouping by that column as well when using aggregate functions..

In your particular case, you need to add substr(custzip, 1,5) to your group by clause.

select substr(custzip, 1,5), custcity, custstate, avg(custbal)
from customer
where custstate = 'wa'
group by substr(custzip, 1,5), custcity, custstate
having avg(custbal) > 100;

Also, please note, you can remove distinct as group by takes care of that as well.

I've also moved custstate = 'wa' to the WHERE criteria. Since WHERE executes prior to HAVING, it will reduce the number of results in which need to be aggregated.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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;

Upvotes: 1

Related Questions