MySQL HAVING Clause return empty set?

Question

I am having a table below, and I need to write code that extract the rows with budget greater than the average budget.

+------+-----------------+--------+
| Code | Name            | Budget |
+------+-----------------+--------+
|   14 | IT              |  65000 |
|   37 | Accounting      |  15000 |
|   59 | Human Resources | 240000 |
|   77 | Research        |  55000 |
+------+-----------------+--------+

I know this works:

SELECT * FROM Departments WHERE Budget > (SELECT AVG(Budget) FROM Departments);

but this looks ugly. This post seems to suggest having clause can simplify the query into:

SELECT * FROM Departments HAVING Budget > AVG(Budget);

but it returns empty set. Any ideas?

Thanks

possible duplicate of http://stackoverflow.com/questions/6924896/having-without-group-by — Ankit Jaiswal, Aug 13 '14 at 04:08

score 3 · Accepted Answer · answered Aug 13 '14 at 04:11

This is because AVG() is aggregation function which should be used GROUP BY or with other Aggregation functions. If not, SELECT would returns single row. for example:

mysql> SELECT * FROM test;
+------+--------+
| code | budget |
+------+--------+
|   14 |  65000 |
|   37 |  15000 |
|   59 | 240000 |
|   77 |  55000 |
+------+--------+
4 rows in set (0.00 sec)

mysql> SELECT code, budget, AVG(budget) FROM test;
+------+--------+-------------+
| code | budget | AVG(budget) |
+------+--------+-------------+
|   14 |  65000 |  93750.0000 | we got one row.
+------+--------+-------------+
1 row in set (0.00 sec)

In this case, HAVING budget > AVG(budget) means 65000 > 93750 which is false, so that returns empty list.

Your first attampt does not look like 'ugly' ;)

This is a VERY good reason why SQL Server doesn't allow this construction (`SELECT *, AVG(BUDGET) FROM Departments`) — Nizam, Aug 13 '14 at 04:34
@Nizam I agree with you. I love MySQL but I hate MySQL's strange non-standard `GROUP BY` behavior. — Jason Heo, Aug 13 '14 at 04:50

score 1 · Answer 2 · answered Aug 13 '14 at 04:30

In mySQL, having an aggregation column with SELECT * will return the first row only.

This SQL Fiddle shows that:

SELECT *, AVG(BUDGET) FROM Departments;

will return only the first row and the average of budget of all rows.

Then, as in your first row, the budget is smaller than the average of budgets, it will return no rows.

I believe your UGLY (I don't think it is ugly) query is a good solution for this.

MySQL HAVING Clause return empty set?

2 Answers2

Linked