Microsoft KB Archive/80284

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

Article ID: 80284

Article Last Modified on 2/14/2005

-

APPLIES TO


 * Microsoft SQL Server 4.21a Standard Edition

-



This article was previously published under Q80284



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=&quot;7066&quot;

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=&quot;7066&quot;

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=&quot;7066&quot; HAVING stor_id=&quot;7066&quot;

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 &quot;unexpected&quot; query results.

Additional query words: Windows NT dblib

Keywords: kbinfo kbprogramming KB80284

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.