Aggregate function/groupby clause error

[redeemasp]

Hi there

I keep getting a

'dbo.AMGR_Client.Contact_Number is invalid in the select list because it is not contained in an aggregate funtion or a GROUP BY clause.'

I want to count all the client names in all of the dbRC_get_G01AccountStatus.Description but get the above error. Any ideas

code:


SELECT dbo.AMGR_Client.Contact_Number, dbo.AMGR_Client.Name, dbRC_get_G09CoreIndustry.Client_Id,
COUNT(dbRC_get_G01AccountStatus.Description) AS Expr1, dbRC_get_G01AccountStatus.Description AS Expr2
FROM dbo.AMGR_Client INNER JOIN
dbRC_get_G09CoreIndustry ON dbo.AMGR_Client.Client_Id = dbRC_get_G09CoreIndustry.Client_Id INNER JOIN
dbRC_get_G01AccountStatus ON dbo.AMGR_Client.Client_Id = dbRC_get_G01AccountStatus.Client_Id INNER JOIN
dbRC_get_G04ORCDivisions ON dbo.AMGR_Client.Client_Id = dbRC_get_G04ORCDivisions.Client_Id
WHERE (dbo.AMGR_Client.Contact_Number = 0)
GROUP BY dbRC_get_G01AccountStatus.Description

 

[mwolf00]

If you have it in the SELECT list and it is not an aggregate function, it need to be in the GROUP BY clause

SELECT     dbo.AMGR_Client.Contact_Number, dbo.AMGR_Client.Name, dbo.ORC_get_G09CoreIndustry.Client_Id,
                      COUNT(dbo.ORC_get_G01AccountStatus.Description) AS Expr1, dbo.ORC_get_G01AccountStatus.Description AS Expr2
FROM         dbo.AMGR_Client INNER JOIN
                      dbo.ORC_get_G09CoreIndustry ON dbo.AMGR_Client.Client_Id = dbo.ORC_get_G09CoreIndustry.Client_Id INNER JOIN
                      dbo.ORC_get_G01AccountStatus ON dbo.AMGR_Client.Client_Id = dbo.ORC_get_G01AccountStatus.Client_Id INNER JOIN
                      dbo.ORC_get_G04ORCDivisions ON dbo.AMGR_Client.Client_Id = dbo.ORC_get_G04ORCDivisions.Client_Id
WHERE     (dbo.AMGR_Client.Contact_Number = 0)
GROUP BY dbo.AMGR_Client.Contact_Number, dbo.AMGR_Client.Name, dbo.ORC_get_G09CoreIndustry.Client_Id,
 dbo.ORC_get_G01AccountStatus.Description

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)