MySQL error: field without grouping is used in the HAVING clause

Question

Is there any way to fix this request so that it works with ONLY_FULL_GROUP_BYenabled?

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

It displays an error message

The non-group field "lower_s" is used in the HAVING clause.

Fiddle

Answer 1

why don't you just use where LENGTH(LOWER(s)) < 5

The use seems to be havingwrong here.

According to sql wiki

HAVING SQL , SQL SELECT , . SQL, WHERE . [1]

Answer 2

HAVING . , .

:

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

Answer 3

:

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

Answer 4

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- , , LOWER(s).