Aggregate function after HAVING clause returning empty set

Question

WARNING: I am aware of this question -- It is NOT like mine because I added a GROUP BY clause in my query and it is still not working.

I need to select the maximum value of the temporary column project_count.

This is the query:

SELECT employee_id, COUNT(*) AS project_count FROM Employees WHERE project_level = 'L1' AND YEAR(Employees.project_date) = '2017' GROUP BY employee_id HAVING project_count = MAX(project_count);

If I remove the HAVING clause, the output is like the following:

+-------------+---------------+
| employee_id | project_count |
+-------------+---------------+
|           1 |             2 |
|           2 |             1 |
|           3 |             3 |
|           5 |             1 |
+-------------+---------------+

However, if I include the HAVING clause (which I need to use to know the maximum value of project_count), the output is an empty set; but it should be like the following:

+-------------+---------------+
| employee_id | project_count |
+-------------+---------------+
|           3 |             3 |
+-------------+---------------+

See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Feb 09 '17 at 18:51
You have two "layers" of grouping but are only specifying one; remember that `MAX(project_count)` is the same as `MAX(COUNT(*))`, grouped by employee_id. I'm surprised MySQL didn't reject the query; and that it decided to return no rows instead of all of them when it accepted it. — Uueerdo, Feb 09 '17 at 19:04

Gurwinder Singh · Accepted Answer · 2017-02-10T19:55:45.460

If you want just one row with max count, you can use limit:

select employee_id, COUNT(*) as project_count
from Employees
where project_level = 'L1'
    and YEAR(Employees.project_date) = 2017
group by employee_id
order by project_count desc limit 1

If you want all of them with max count:

select employee_id, COUNT(*) as project_count
from Employees
where project_level = 'L1'
    and YEAR(Employees.project_date) = 2017
group by employee_id
having COUNT(*) = (
        select max(cnt)
        from (
            select COUNT(*) cnt
            from Employees
            where project_level = 'L1'
                and YEAR(Employees.project_date) = 2017
            group by employee_id
            ) t
        )

Aggregate function after HAVING clause returning empty set

1 Answers1