SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Cannot use an aggregate or a subquery in a express		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

magmo
Constraint Violating Yak Guru

465 Posts
Posted - 08/21/2012 :  06:26:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 08/21/2012 :  06:42:42  Show Profile  Reply with Quote
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
Go to Top of Page

magmo
Constraint Violating Yak Guru

465 Posts
Posted - 08/21/2012 :  06:48:32  Show Profile  Reply with Quote
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"
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 08/21/2012 :  06:56:52  Show Profile  Reply with Quote
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


or 
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
Edited by - sunitabeck on 08/21/2012 06:57:33
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3328 Posts
Posted - 08/21/2012 :  06:58:37  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

magmo
Constraint Violating Yak Guru

465 Posts
Posted - 08/21/2012 :  07:19:27  Show Profile  Reply with Quote
Thank you both very much, it works as I need it to now. :)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000