What is the difference between HAVING and WHERE in Aggregate Queries?

Just today, I ran into one of the best explanations of the differences between the HAVING clause and the WHERE clause in aggregate queries (or Totals queries as they are called in Access).

It was in the Microsoft Public Newsgroups by fellow MVP, Vanderghast. The following is used with his permission:


The WHERE clause is evaluated before the AGGREGATION/GROUP formation, while the HAVING clause is evaluated after.

As example, you can eliminate records with a negative value before summing the values:

SELECT itemID, SUM(value)
FROM somewhere
WHERE value >= 0
GROUP BY itemID

Or, sum everything, and them, remove the groups where their sum is negative:

SELECT itemID, SUM(value)
FROM somwhere
GROUP BY itemID
HAVING SUM(value) >=0

You cannot have a WHERE clause on an aggregation:

WHERE SUM(something) > 0
since the aggregation does not exists, yet.

You cannot have an HAVING on a non-aggregated, non-grouped field, since that field is 'lost' after the aggregate:

SELECT a, SUM(b)
FROM table
GROUP BY a
HAVING c > 0

as example, maybe we need some data:

        a       b        c
        10      1       -3
        10      2        4
        11      4       -5

After the aggregation, we have

a       SUM(b)
10      3
11      4

what will be 'c' in the HAVING clause?

On the other hand:

SELECT itemID, SUM(b)
FROM table
WHERE c > 0
GROUP BY itemID

makes sense, and the result is now

a     SUM(b)
10    2

Vanderghast, Access MVP

[top]