DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS

 

No-Grouping Column in GROUP BY SELECT List
By Kalman Toth, M.Phil. Physics, M.Phil. Comp. Science, MCDBA, MCITP
April 14, 2009

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 (minimum) or drop it from the SELECT list.  However, with SQL Server 2005 and SQL Server 2008 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 in the AdventureWorks database:

Here is the script:

SELECT   Shelf,

         COUNT(ProductID) AS ProductCount

FROM     Production.ProductInventory

GROUP BY Shelf

ORDER BY Shelf

 

The partial result set:

Shelf ProductCount
A 81
B 36
C 55
D 50
E 85
F 59

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)                                   AS ProductCount,

         QUOTENAME(min(Name)) + ' - ' + QUOTENAME(max(Name))  AS ProductRange

FROM     Production.ProductInventory i

         INNER JOIN Production.Product p

           ON i.ProductID = p.ProductID

GROUP BY Shelf

ORDER BY Shelf

Here is the partial result set:

Shelf ProductCount ProductRange
A 81 [Adjustable Race] - [Touring Rear Wheel]
B 36 [Adjustable Race] - [Touring Front Wheel]
C 55 [Chain] - [Thin-Jam Hex Nut 9]
D 50 [Guide Pulley] - [Touring End Caps]
E 85 [Cone-Shaped Race] - [Touring Pedal]
F 59 [Down Tube] - [Stem]
G 96 [Decal 1] - [Thin-Jam Lock Nut 9]

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 using a correlated subquery (subselect in select).

Following is the query:

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:

Shelf ProductCount ProductList
A 81  Adjustable Race, Adjustable Race, BB Ball Bearing, BB Ball Bearing, Bearing Ball, Bearing Ball, Blade, Chain, Chain, Chain Stays, Chain Stays, Chainring, Chainring, Chainring Bolts, Chainring Bolts, Chainring Nut, Chainring Nut, Crown Race, Crown Race, Decal 1, Decal 2, Fork Crown, Fork End, Freewheel, Front Brakes, Front Derailleur, Front Derailleur Cage, Front Derailleur Linkage, Handlebar Tube, Head Tube, Headset Ball Bearings, Headset Ball Bearings, HL Bottom Bracket, HL Bottom Bracket, HL Crankarm, HL Crankarm, HL Crankset, HL Crankset, HL Fork, HL Hub, HL Mountain Rear Wheel, HL Road Rear Wheel, LL Bottom Bracket, LL Bottom Bracket, LL Crankarm, LL Crankarm, LL Crankset, LL Crankset, LL Fork, LL Hub, LL Mountain Rear Wheel, LL Road Rear Wheel, Metal Angle, Metal Bar 1, Metal Bar 2, ML Bottom Bracket, ML Bottom Bracket, ML Crankarm, ML Crankarm, ML Crankset, ML Crankset, ML Fork, ML Mountain Rear Wheel, ML Road Rear Wheel, Mountain End Caps, Mountain End Caps, Paint - Black, Paint - Black, Paint - Blue, Paint - Blue, Paint - Red, Paint - Red, Paint - Silver, Paint - Silver, Paint - Yellow, Paint - Yellow, Road End Caps, Road End Caps, Touring End Caps, Touring End Caps, Touring Rear Wheel
B 36  Adjustable Race, BB Ball Bearing, Bearing Ball, Blade, Blade, Down Tube, Handlebar Tube, Handlebar Tube, Head Tube, Head Tube, Headset Ball Bearings, HL Bottom Bracket, HL Hub, HL Hub, HL Mountain Front Wheel, HL Road Front Wheel, HL Shell, LL Bottom Bracket, LL Hub, LL Hub, LL Mountain Front Wheel, LL Road Front Wheel, LL Shell, Metal Angle, Metal Bar 1, Metal Bar 2, ML Bottom Bracket, ML Mountain Front Wheel, ML Road Front Wheel, Paint - Black, Paint - Blue, Paint - Red, Paint - Silver, Paint - Yellow, Spokes, Touring Front Wheel
C 55  Chain, Chain Stays, Chainring, Chainring Bolts, Chainring Nut, Crown Race, Fork Crown, Fork End, Freewheel, Front Derailleur, Front Derailleur Cage, Front Derailleur Linkage, Guide Pulley, Hex Nut 12, Hex Nut 13, Hex Nut 16, Hex Nut 17, Hex Nut 22, Hex Nut 23, Hex Nut 5, Hex Nut 6, Hex Nut 7, Hex Nut 8, Hex Nut 9, HL Crankarm, HL Fork, HL Grip Tape, LL Crankarm, LL Fork, LL Grip Tape, Metal Plate 1, Metal Plate 1, Metal Plate 2, Metal Plate 2, Metal Plate 3, Metal Plate 3, ML Crankarm, ML Fork, ML Grip Tape, Thin-Jam Hex Nut 1, Thin-Jam Hex Nut 10, Thin-Jam Hex Nut 11, Thin-Jam Hex Nut 12, Thin-Jam Hex Nut 13, Thin-Jam Hex Nut 14, Thin-Jam Hex Nut 15, Thin-Jam Hex Nut 16, Thin-Jam Hex Nut 2, Thin-Jam Hex Nut 3, Thin-Jam Hex Nut 4, Thin-Jam Hex Nut 5, Thin-Jam Hex Nut 6, Thin-Jam Hex Nut 7, Thin-Jam Hex Nut 8, Thin-Jam Hex Nut 9

Related articles:

How do I use a SELECT list alias in the WHERE or GROUP BY clause?

Msg 8120 Column 'xxx' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (SQL-42000)

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE