aggregate function or the GROUP BY clause

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

aggregate function or the GROUP BY clause

Author

Topic

bangingtunes
Starting Member

23 Posts

Posted - 2001-12-06 : 19:10:05

When i run the following statement i get the following error

Column 'Items.Label' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SELECT TOP 5 Items.ItemID, Number=COUNT(*), Items.Availability, Items.Label, Items.Artist, Items.Title, Items.ExtraInfo, Items.mp3s, Items.ItemDescription, Items.ItemPrice, Items.ItemName, Items.released FROM Items INNER JOIN OrderItems ON Items.ItemID=OrderItems.ItemID WHERE OrderItems.Account IN (SELECT Account FROM OrderItems WHERE ItemID='" & ItemID & "') GROUP BY Items.ItemID ORDER BY Number DESC

It has something to do with the number=count(*) that is in there. How can i get around this? please help.

Kris

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2001-12-06 : 19:14:04

Hi

When you do a count or any other aggrigate, you need to GROUP BY everything else in your select list.

In your case


Items.ItemID, Items.Availability, Items.Label, Items.Artist,
Items.Title, Items.ExtraInfo, Items.mp3s, Items.ItemDescription, 
Items.ItemPrice, Items.ItemName, Items.released

Hope that helps

Damian

Edited by - merkin on 12/06/2001 19:14:29

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2001-12-06 : 19:17:47

Every column that returns a scalar value (ie Not Aggregated) must be included in the "Group By" Clause.

On the other hand a item can appear in the "group by" clause without being in the "select" clause

DavidM
It gets windy at a thousand feet...."Tutorial D"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources