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

Answer 1

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]