INF: Mix of Aggregate Functions & Column Names w/out GROUP BY (80284)

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.