ID Number: Q80284
1.10a 1.11 4.20
OS/2
Summary:
When both aggregate functions and table columns are included in the
select list of a query without the GROUP BY clause, the WHERE clause
applies only to the rows included in the calculation of the aggregate
function, but does not restrict the rows returned by the query.
Similarly, if a HAVING clause is used without a GROUP BY clause (which
is proper in SQL Server), a HAVING clause restricts the rows returned
by the query but does not affect the calculation of the aggregate
function.
More Information:
Consider the following query
select count (stor_id), stor_id from stores WHERE stor_id="7066"
which returns the following results:
stor_id
------ -------
1 7066
1 7067
1 7131
1 8042
1 6380
1 7896
Note that the condition in the WHERE clause does not restrict the rows
returned.
Similarly, the following query
select count (stor_id), stor_id from stores HAVING stor_id="7066"
returns the following results:
stor_id
------ -------
6 7066
Note also that the condition in the HAVING clause does not affect the
calculation of the aggregate function.
If both WHERE and HAVING are used, then both the returned rows and the
calculation of the aggregate function are restricted. The following
query shows this clearly:
select count (stor_id), stor_id from stores WHERE stor_id="7066"
HAVING stor_id="7066"
This query returns:
stor_id
------ -------
1 7066
The above properties of the WHERE and HAVING clauses also apply to all
other types of aggregate functions.
Although it is generally considered meaningless to mix column names
with aggregate functions in the select list without GROUP BY, it is
used in practice anyway. Therefore, special caution should be taken
when using these types of queries because users can easily
misinterpret their meaning and get "unexpected" query results.
Additional reference words: 1.10a 1.11 4.20