MySQL error: Non-grouping field is used in HAVING clause

Question

Is there a way to correct this query so that it works with ONLY_FULL_GROUP_BY enabled?

SELECT LOWER(s) AS lower_s, SUM(i) AS sum_i
FROM t
GROUP BY 1
HAVING LENGTH(lower_s) < 5

It gives the error message

Non-grouping field 'lower_s' is used in HAVING clause

Fiddle

In SQL Server you can't use the alias in the having clause. Maybe try `having(LENGTH(LOWER(s)) < 5`? — Sean, Dec 21 '15 at 07:07

score 5 · Accepted Answer · answered Dec 21 '15 at 07:07

5

why dont you just use where LENGTH(LOWER(s)) < 5

It seems the using of having is not right here.

According to having sql wiki

A HAVING clause in SQL specifies that an SQL SELECT statement should only return rows where aggregate values meet the specified conditions. It was added to the SQL language because the WHERE keyword could not be used with aggregate functions.[1]

answered Dec 21 '15 at 07:07

amow

2,203
11
19

Accepted because "HAVING is only for aggregate fields" is something I can remember. Hopefully it doesn't affect performance. – AndreKR Dec 21 '15 at 07:59

Saharsh Shah · Answer 2 · 2015-12-22T07:22:05.140

4

No need to use HAVING clause in your query. Check below query that will satisfy your requirement.

Try this:

SELECT LOWER(s) AS lower_s, SUM(i) AS sum_i
FROM t
WHERE LENGTH(LOWER(s)) < 5
GROUP BY 1;

edited Dec 22 '15 at 07:22

answered Dec 21 '15 at 07:08

Saharsh Shah

28,687
8
48
83

1

Length(s) is not always the same as length(lower(s)). (E.g. German `STRAßE` => `strasse`.) – jarlh Dec 22 '15 at 07:21
@jarlh OK, I am not aware of that. Thanks for that, I had updated my answer. – Saharsh Shah Dec 22 '15 at 07:22
@jarlh that is actually a weird comment. ß is a lowercase letter so lowercasing ß does not make ss. – warly Apr 13 '18 at 11:16
@warly, Well, poor example I understand. (I probably meant the opposite direction... German is not my first language, or second.) But the fact remains, in some languages the length may change when you do upper/lower for certain characters/combinations. – jarlh Apr 13 '18 at 11:22

score 1 · Answer 3 · answered Dec 21 '15 at 07:10

1

A workaround would be:

SELECT LOWER(s) AS lower_s, SUM(i) AS sum_i 
FROM t 
WHERE LENGTH(lower_s) < 5 
GROUP BY 1

answered Dec 21 '15 at 07:10

Academiphile

1,434
2
13
21

1

What is different from @Saharsh Shah answer – Sadikhasan Dec 21 '15 at 07:13

jarlh · Answer 4 · 2015-12-22T07:16:24.600

1

select lower_s, SUM(i) AS sum_i
from
(
    SELECT LOWER(s) AS lower_s, i
    FROM t
) dt
where LENGTH(lower_s) < 5
GROUP BY lower_s

ANSI SQL compliant answer, having a derived table so you only have to write the LOWER(s) expression once.

edited Dec 22 '15 at 07:16

answered Dec 21 '15 at 07:51

jarlh

42,561
8
45
63

MySQL error: Non-grouping field is used in HAVING clause

4 Answers4