No-Grouping Column in GROUP BY SELECT List
By Kalman Toth, M.Phil., M.Phil., MCDBA, MCITP
October 20, 2007

A frequent message when working with GROUP BY-s :  Column 'X' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. You have 3 choices: add the column to the grouping, apply an aggregate function on the column such as MIN or drop it from the SELECT list.  However, with SQL Server 2005 there is a 3rd alternative: actually include non-grouping column in the SELECT list. This is a new development. Here is a simple GROUP BY query:

Here is the script:

SELECT Shelf,
COUNT(ProductID) ProductCount
FROM Production.ProductInventory
GROUP BY Shelf
ORDER BY Shelf

The partial result set:

Surely this is useful information but we have no idea what is on the shelves. Since we cannot include the ProductName in the SELECT list of a GROUP BY we shall construct a range using the MIN and MAX aggregate functions. This is the script:

SELECT Shelf,
COUNT(i.ProductID) ProductCount,
'['+min (Name)+'] - ['+max(Name)+']' ProductRange
FROM Production.ProductInventory i
JOIN Production.Product p
ON i.ProductID = p.ProductID
GROUP BY Shelf
ORDER BY Shelf

Here is the partial result set:

Certainly much better. At least now we have an idea what kind of products or parts are on the shelves. Yet it is just an idea. We are not certain though. With one of the magic new features of SQL Server 2005, we can coustruct the entire list of items even though it is a list not in the GROUP BY clause.

This is the code script:

SELECT Shelf,
COUNT(i.ProductID) ProductCount,
(Stuff((
Select ', ' + p.Name as [text()]
From Production.Product p
JOIN Production.ProductInventory ii
ON ii.ProductID=p.ProductID
WHERE i.Shelf = ii.Shelf
Order by p.Name
For XML Path ('')), 1, 1, '')) ProductList
FROM Production.ProductInventory i
GROUP BY Shelf
ORDER BY Shelf

Here is the partial resul list: