Cannot use an aggregate or a subquery in a express

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 2005 Forums

Transact-SQL (2005)

Cannot use an aggregate or a subquery in a express

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-21 : 06:26:27

Hi

I try to use the following query


SELECT        COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.GreenGrade AS Green, COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) 
                         * dbo.Grade.YellowGrade AS Yellow, COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.RedGrade AS Red, dbo.Avdelning.Name, 
                         dbo.Category.CatgoryName
FROM            dbo.SurveyAnswers INNER JOIN
                         dbo.SurveyAnswerInfo ON dbo.SurveyAnswers.SurveyAnswerInfoID = dbo.SurveyAnswerInfo.SurveyAnswerInfoID INNER JOIN
                         dbo.Question ON dbo.SurveyAnswers.QuestionID = dbo.Question.QuestionID INNER JOIN
                         dbo.Category ON dbo.Question.CategoryID = dbo.Category.CategoryID INNER JOIN
                         dbo.Grade ON dbo.Category.CategoryID = dbo.Grade.CategoryID INNER JOIN
                         dbo.Users ON dbo.SurveyAnswerInfo.UserID = dbo.Users.UserID INNER JOIN
                         dbo.Avdelning ON dbo.Users.AvdID = dbo.Avdelning.AvdID
WHERE        (dbo.Users.CustID = 2)
GROUP BY dbo.Avdelning.Name, dbo.Category.CatgoryName, COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.RedGrade, 
                         COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.YellowGrade, COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) 
                         * dbo.Grade.GreenGrade

But I get a "Cannot use an aggregate or a subquery in a expression used for by list of a GROUP BY clause.

Can someone please show me what I need to change?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-21 : 06:42:42

Remove the aggregates in the group by clause. From what I can tell, that logically that is what you need as well.

SELECT        COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.GreenGrade AS Green, COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) 
                         * dbo.Grade.YellowGrade AS Yellow, COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.RedGrade AS Red, dbo.Avdelning.Name, 
                         dbo.Category.CatgoryName
FROM            dbo.SurveyAnswers INNER JOIN
                         dbo.SurveyAnswerInfo ON dbo.SurveyAnswers.SurveyAnswerInfoID = dbo.SurveyAnswerInfo.SurveyAnswerInfoID INNER JOIN
                         dbo.Question ON dbo.SurveyAnswers.QuestionID = dbo.Question.QuestionID INNER JOIN
                         dbo.Category ON dbo.Question.CategoryID = dbo.Category.CategoryID INNER JOIN
                         dbo.Grade ON dbo.Category.CategoryID = dbo.Grade.CategoryID INNER JOIN
                         dbo.Users ON dbo.SurveyAnswerInfo.UserID = dbo.Users.UserID INNER JOIN
                         dbo.Avdelning ON dbo.Users.AvdID = dbo.Avdelning.AvdID
WHERE        (dbo.Users.CustID = 2)
GROUP BY dbo.Avdelning.Name, dbo.Category.CatgoryName  , COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.RedGrade, 
                         COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.YellowGrade, COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) 
                         * dbo.Grade.GreenGrade

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-21 : 06:48:32

Thanks, but when I do that I get this error...

"dbo.Grade.GreenGrade' is invalid in the select list beacuse it is not contained in either an aggregate function or the GROUP BY clause"

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-21 : 06:56:52

Yes, of course. I noticed that only after I posted it. Not sure which of the following is logically correct in your problem, but it should be one of these. If it is neither, can you post some sample data?:

SELECT
	COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID * dbo.Grade.GreenGrade)  AS 
	Green,
	COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID * dbo.Grade.YellowGrade) 
	 AS Yellow,
	COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID * dbo.Grade.RedGrade)  AS 
	Red,
	dbo.Avdelning.Name,
	dbo.Category.CatgoryName
FROM            dbo.SurveyAnswers INNER JOIN
                         dbo.SurveyAnswerInfo ON dbo.SurveyAnswers.SurveyAnswerInfoID = dbo.SurveyAnswerInfo.SurveyAnswerInfoID INNER JOIN
                         dbo.Question ON dbo.SurveyAnswers.QuestionID = dbo.Question.QuestionID INNER JOIN
                         dbo.Category ON dbo.Question.CategoryID = dbo.Category.CategoryID INNER JOIN
                         dbo.Grade ON dbo.Category.CategoryID = dbo.Grade.CategoryID INNER JOIN
                         dbo.Users ON dbo.SurveyAnswerInfo.UserID = dbo.Users.UserID INNER JOIN
                         dbo.Avdelning ON dbo.Users.AvdID = dbo.Avdelning.AvdID
WHERE        (dbo.Users.CustID = 2)
GROUP BY dbo.Avdelning.Name, dbo.Category.CatgoryName

SELECT        COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.GreenGrade AS Green, COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) 
                         * dbo.Grade.YellowGrade AS Yellow, COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.RedGrade AS Red, dbo.Avdelning.Name, 
                         dbo.Category.CatgoryName
FROM            dbo.SurveyAnswers INNER JOIN
                         dbo.SurveyAnswerInfo ON dbo.SurveyAnswers.SurveyAnswerInfoID = dbo.SurveyAnswerInfo.SurveyAnswerInfoID INNER JOIN
                         dbo.Question ON dbo.SurveyAnswers.QuestionID = dbo.Question.QuestionID INNER JOIN
                         dbo.Category ON dbo.Question.CategoryID = dbo.Category.CategoryID INNER JOIN
                         dbo.Grade ON dbo.Category.CategoryID = dbo.Grade.CategoryID INNER JOIN
                         dbo.Users ON dbo.SurveyAnswerInfo.UserID = dbo.Users.UserID INNER JOIN
                         dbo.Avdelning ON dbo.Users.AvdID = dbo.Avdelning.AvdID
WHERE        (dbo.Users.CustID = 2)
GROUP BY dbo.Avdelning.Name, dbo.Category.CatgoryName,
 dbo.Grade.GreenGrade,
dbo.Grade.YellowGrade,
dbo.Grade.RedGrade

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-21 : 06:58:37

What do yoou want for COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * dbo.Grade.GreenGrade?
Say you have for a name two values for SurveyAnswerInfoID {1,2} 2 for the distinct - which value do yoou want for GreenGrade - if it is a constant for the group - i.e. CategoryID is the lowest level then you can
COUNT(DISTINCT dbo.SurveyAnswerInfo.SurveyAnswerInfoID) * max(dbo.Grade.GreenGrade)
and similarly for the other grades.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

magmo
Aged Yak Warrior

558 Posts

Posted - 2012-08-21 : 07:19:27

Thank you both very much, it works as I need it to now. :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources