error with aggregate function/having 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

Transact-SQL (2000)

error with aggregate function/having clause

Author

Topic

hueby
Posting Yak Master

127 Posts

Posted - 2004-12-03 : 14:18:18

Hi all, This statment works fine for summing my hours into Hourstotal and grouping it by P.O. BUT, when I try to add a Having clause to specify on jobs that arn't completed (jobs.completed = 0) I get this error:

quote:
Column 'dbo.Jobs.Completed' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8121, Level 16, State 1, Line 12
Column 'dbo.Jobs.Completed' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Here is the code...

SELECT     SUM(dbo.Hours.ExpFinish + dbo.Hours.ExpRepair + dbo.Hours.ExpOther) AS HoursTotal, dbo.Hours.Purchord, dbo.Jobs.JobNo, dbo.Jobs.Descr, 
                      dbo.Jobs.estHours, dbo.Jobs.Completed
FROM         dbo.Hours INNER JOIN
                      dbo.Jobs ON dbo.Hours.Purchord = dbo.Jobs.Purchord
GROUP BY dbo.Hours.Purchord, dbo.Jobs.JobNo, dbo.Jobs.Descr, dbo.Jobs.estHours
HAVING      (dbo.Jobs.Completed = 0)

How can I accomplish this??

chadmat
The Chadinator

1974 Posts

Posted - 2004-12-03 : 14:26:37

add dbo.Jobs.Completed to the GROUP BY clause


SELECT     SUM(dbo.Hours.ExpFinish + dbo.Hours.ExpRepair + dbo.Hours.ExpOther) AS HoursTotal, dbo.Hours.Purchord, dbo.Jobs.JobNo, dbo.Jobs.Descr, 
                      dbo.Jobs.estHours, dbo.Jobs.Completed
FROM         dbo.Hours INNER JOIN
                      dbo.Jobs ON dbo.Hours.Purchord = dbo.Jobs.Purchord
GROUP BY dbo.Hours.Purchord, dbo.Jobs.JobNo, dbo.Jobs.Descr, dbo.Jobs.estHours, dbo.Jobs.Completed
HAVING      (dbo.Jobs.Completed = 0)

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

hueby
Posting Yak Master

127 Posts

Posted - 2004-12-03 : 14:30:36

Ohhhhhhh!!! Thank you Chad!

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-03 : 15:48:30

The HAVING clause is used to filter on aggregates (e.g., SUM, COUNT, etc.); not to check scalar values. It sounds like all you want to do is add your filter to the WHERE clause.

Ex.
WHERE dbo.Jobs.Completed = 0

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources