When to use the WHERE Before the Group BY Vs the Having after the Group By

Question

Here is my query that only runs correctly when using the restrictive condition in the WHERE before the Group By and not when using the restrictive condition in the Having after the Group By. I thought that the Having acts like the Where clause and should be used when there is a need to do a Group By but this query below is not following that logic:

/* Calculate the total actual costs for all projects not over-budgeted for any manager. Display the manager's name, project, and sums for their individual projects.

Select mg_name, p_name, sum(actual_cost), sum(expected_cost)
From Project join manager on p_manager = mg_number
Where actual_cost <= expected_cost
Group by mg_name;

Incorrect query below that gives an error in MySQL noting that the actual_cost is not known in the Having (Error Code: 1054. Unknown column 'actual_cost' in 'having clause')

Select mg_name, p_name, sum(actual_cost), sum(expected_cost)
From Project join manager on p_manager = mg_number
Group by mg_name
Having actual_cost <= expected_cost;

Can someone explain why the first query works and the second does not?

*Can someone explain why the first query works and the second does not?* Think like WHERE processes source columns (columns from source tables) whereas HAVING output ones (columns/expressions which are or may be listed in output). — Akina, Sep 14 '20 at 19:28
You can't use column aliases in a `WHERE` clause; see the [documentation](https://dev.mysql.com/doc/refman/8.0/en/problems-with-alias.html) — Nick, Sep 14 '20 at 22:29

score 0 · Answer 1 · answered Sep 14 '20 at 19:27

where applies to select rows before the group by (so the sums will not include the selected rows). having applies to the selected fields after the group by. It can use aliases from the selected fields, or expressions including sum, so you would do:

select mg_name, p_name, sum(actual_cost), sum(expected_cost)
from project join manager on p_manager=mg_number
group by mg_name
having sum(actual_cost) <= sum(expected_cost)

or

select mg_name, p_name, sum(actual_cost) manager_total_actual_cost, sum(expected_cost) manager_total_expected_cost
from project join manager on p_manager=mg_number
group by mg_name
having manager_total_actual_cost <= manager_total_expected_cost

You could consider it shorthand for using a subquery and while:

select mg_name, p_name, manager_total_actual_cost, manager_total_expected_cost
from (
    select mg_name, p_name, sum(actual_cost) manager_total_actual_cost, sum(expected_cost) manager_total_expected_cost
    from project join manager on p_manager=mg_number
    group by mg_name
) manager_totals
where manager_total_actual_cost <= manager_total_expected_cost

if this answer was helpful to you, consider accepting it (clicking on the big check mark to the left of the answer) and/or upvoting it — ysth, Sep 14 '20 at 20:55

score 0 · Answer 2 · answered Sep 14 '20 at 20:08

You can use HAVING with aggregate functions.

Basically:

HAVING is like WHERE but operates on grouped records returned by a GROUP BY.
HAVING applies to summarized group records, whereas WHERE applies to individual records.
Only the groups that meet the HAVING criteria will be returned.
HAVING requires that a GROUP BY clause is present.
WHERE and HAVING can be used in the same query.

Ex.: SELECT COUNT(Id), Country FROM Customer WHERE Country <> 'USA' GROUP BY Country HAVING COUNT(Id) >= 9 ORDER BY COUNT(Id) DESC

You can find a good reference about it in this dofactory article

Another good example can be seen using w3schools:

SELECT customerID, count(orderid) FROM orders group by CustomerID having count(orderid) >1;

If you try to use any aggregate function after WHERE, you're gonna get an error. To sumarize, aggregate functions will work after SELECT and after HAVING, never after WHERE.

When to use the WHERE Before the Group BY Vs the Having after the Group By

2 Answers2