3

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.

Restnom
  • 124
  • 1
  • 15

3 Answers3

1

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.

sgeddes
  • 62,311
  • 6
  • 61
  • 83
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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Just curious, when would `distinct` ever be used with `group by`? Also, isn't it best practice to put non-aggregated criteria in the `where` clause instead of `having`? – sgeddes Sep 24 '14 at 03:30
  • The distinct was a mistake. Can you explain to me why it works when the substr(custzip,1,5) goes at the end of there group by clause and not in the middle or on the front? Or was I doing something completely different? – Restnom Sep 24 '14 at 03:30
  • @Restnom -- The order of the columns in the `select` and `group by` clauses are irrelevant. You just need to ensure when using aggregation (i.e. `avg()`), the non-aggregated columns are included in the `group by` clause, in this case, `substr(custzip, 1,5)` was left out. – sgeddes Sep 24 '14 at 04:09
  • @Restnom . . . In the first case, the `substr()` expression is in the `group by`, which is why it works. In the second, it is in an aggregation function. This has nothing to do with the ordering of the expressions in either the `select` or the `group by`. – Gordon Linoff Sep 24 '14 at 11:23
0

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.

Pang
  • 9,564
  • 146
  • 81
  • 122
Tangoo
  • 1,329
  • 4
  • 14
  • 34