Aggregate Function Problem. - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Aggregate Function Problem.

Author

Topic

DavidChel
Constraint Violating Yak Guru

474 Posts

Posted - 2008-10-09 : 15:33:25

Consider the following:

SELECT Inonhd.Fac,  
					Inonhd.fpartno,  
					Inonhd.fpartrev,  
					Inonhd.fcudrev,  
					Inonhd.fbinno,  
					Inonhd.flocation,  
					sum(Inonhd.fonhand) as fonhand,  
					Inonhd.flot,  
					Inmast.fdescript,  
					Inmast.fmeasure,  
					Inmast.fstdcost,  
					Inmast.f2totcost,  
					Inmast.flastcost,  
					Inmast.fgroup,  
					Inmast.fprodcl,  
					Inmast.favgcost  
 
				FROM m2mdata01.dbo.inonhd inonhd INNER JOIN m2mdata01.dbo.inmast inmast  
				ON UPPER(inMast.Fac) = UPPER(Inonhd.Fac)  
				AND UPPER(Inmast.fpartno) = UPPER(Inonhd.fpartno)  
				AND UPPER(Inmast.fRev) = UPPER(Inonhd.fpartrev)

Of course I get the error:

SQL Server Database Error: Column 'inonhd.fac' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

Over and over again.

However, when I put all those fields in the group by clause I don't get the data I want either. Should I just be doing these sums with a nested query?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-10-09 : 15:35:28

Use the ROW_NUMBER function to achieve this if you are using SQL Server 2005. If you are using SQL Server 2000, the best approach involves a derived table.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-10-09 : 16:44:21

Two things, is your database Case Sensitive? If not, then you are wasting a lot of resources using the UPPER function. Secondly, what do you want the results to be? Obviously, you need to group by in order include an aggregate function. So, I'm confused what you are trying to do..?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources