FIX: GROUP BY ALL on a View Shows Columns Not in View

• Microsoft SQL Server versions 6.0, 6.5

SYMPTOMS

Transact-SQL provides the ALL keyword in the GROUP BY clause. ALL is meaningful only when the SELECT statement in which it is used also includes a WHERE clause.

If you use ALL, the query results include all groups produced by the GROUP BY clause, even if some of the groups do not have any rows that meet the search conditions. Without ALL, a SELECT statement that includes GROUP BY does not show groups for which no rows qualify.

When using a GROUP BY ALL clause against a view, all of the columns that exist in the underlying table are inadvertently included in the results, even though they are not used in the view.

WORKAROUND

To work around this problem, do one of the following:

• Obtain the hotfix mentioned in the STATUS section of this article.
  
  -or-



• Create a table that does not contain any data, and use that table in one side of the UNION.
  
  -or-



• Use statements as in the following example:

  
  CREATE VIEW vYes
        AS
        SELECT  fldname
        FROM     tblYesNo
        WHERE  fldname='YES'
        UNION
        SELECT  fldname
        FROM     tblYesNo
        WHERE  fldname='YES' 

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.0.

Microsoft has confirmed this to be a problem in SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

For more information, contact your primary support provider.

MORE INFORMATION

For more information on the ALL keyword, see the "SELECT Statement" topic in the SQL Server Books Online.

Additional query words: sp sp5security

Keywords          : SSrvProg SSrvTran_SQL kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.0,6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: April 21, 1999