SQL GROUP BY and HAVING
So I am not getting this error 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 clause" and suggests adding "substr (CUSTZIP, 1,5), AVG (CUSTBAL)" to the group by clause, but that won't work either. What I am trying to do is list postal codes and average balances by city in WA only and have a balance of over $ 100. Can someone help me point out my mistake. I'm sure this is something simple, but I don't seem to understand it at a basic level.
source to share
The error you are getting is that you are trying to do an aggregation (i.e. sum
, avg
etc.) and not including all the columns in the sentence select
in your sentence group by
. If you select a column, then you must group that column also when using aggregate functions.
In your specific case, you need to add substr(custzip, 1,5)
to your proposal group by
.
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 that you can uninstall distinct
as it group by
will take care of this.
I also moved custstate = 'wa'
to the criteria WHERE
. Since it WHERE
runs before HAVING
, this will reduce the number of results that need to be aggregated.
source to share
The problem is not the sentence having
. This is substr(CUSTZIP, 1, 5)
. Here's 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, it is select distinct
almost never required with group by
.
Alternatively, you can use the 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;
source to share