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

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.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"

          stor_id
   ------ -------
       1  7066
       1  7067
       1  7131
       1  8042
       1  6380
       1  7896

Similarly, the following query

   select count (stor_id), stor_id from stores HAVING stor_id="7066"

          stor_id
   ------ -------
       6  7066

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"

          stor_id
   ------ -------
       1  7066

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.