How to use having on un-aggregate field in MySQL?

Question

select xx as fieldA from ... group by xxx having fieldA is not null

I found that having has no effect

Please close it,it's a bug of 5.0 – wamp Sep 10 '10 at 07:59 — wamp, Sep 10 '10 at 07:59

score 0 · Answer 1 · answered Sep 10 '10 at 07:55

0

May be :

 select xx as fieldA from ... where fieldA is not null group by xxx having

help you

answered Sep 10 '10 at 07:55

Michael Pakhantsov

24,855
6
60
59

This won't work because aliases are not available in `GROUP BY`. (They are in `ORDER BY` and `HAVING`). Changing to `WHERE fieldA IS NOT NULL` would change the number of rows being grouped, possibly changing the original intent. – Rick James May 07 '17 at 02:57

score 0 · Answer 2 · answered May 07 '17 at 02:55

You should include all non-aggregate columns in the GROUP BY. 5.7.5 introduced the SQL_MODE of only_full_group_by to enforce such (while not complaining about some cases where it is actually OK).

When you have a non-aggregate column, the code is free to provide any value it likes for such a column (xx in your case). Given that, then how could the HAVING make any rational sense.

This is not a bug; it is a user error.

How to use having on un-aggregate field in MySQL?

2 Answers2