group by Error! - 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

SQL Server 2008 Forums

Transact-SQL (2008)

group by Error!

Author

Topic

beyk
Starting Member

3 Posts

Posted - 2012-09-11 : 14:24:08

hi
this is my sql statement
<<<<
SELECT (SELECT ExamStuInSal_View.Grade
WHERE (ExamStuInSal_View.Exam_Type_Id = 1)) AS GradeMid1,
(SELECT ExamStuInSal_View.Grade
WHERE (ExamStuInSal_View.Exam_Type_Id = 2)) AS GradeTerm1,
(SELECT ExamStuInSal_View.Grade
WHERE (ExamStuInSal_View.Exam_Type_Id = 3)) AS GradeMid2,
(SELECT ExamStuInSal_View.Grade
WHERE (ExamStuInSal_View.Exam_Type_Id = 4)) AS GradeTerm2
FROM ExamStuInSal_View
WHERE (Student_Id = @user_id) AND ([Sal_Id ] = @Sal_Id)
GROUP BY Dars_Id
>>>>>>>
but whan i want use group by dars_id, this Error be shown:
< column 'ExamStuInSal_View.Exam_Type_Id' is invalid in the select list because it is not contained in either an aggregate function or the group by clause. >

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 14:33:19

[code]
SELECT MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 1 THEN ExamStuInSal_View.Grade END) AS GradeMid1,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 2 THEN ExamStuInSal_View.Grade END) AS GradeTerm1,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 3 THEN ExamStuInSal_View.Grade END) AS GradeMid2,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 4 THEN ExamStuInSal_View.Grade END) AS GradeTerm2
FROM ExamStuInSal_View
WHERE (Student_Id = @user_id) AND ([Sal_Id ] = @Sal_Id)
GROUP BY Dars_Id
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

beyk
Starting Member

3 Posts

Posted - 2012-09-11 : 14:44:51

quote:

Originally posted by visakh16


SELECT MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 1 THEN ExamStuInSal_View.Grade END) AS GradeMid1,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 2 THEN ExamStuInSal_View.Grade END) AS GradeTerm1,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 3 THEN ExamStuInSal_View.Grade END) AS GradeMid2,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 4 THEN ExamStuInSal_View.Grade END) AS GradeTerm2
FROM ExamStuInSal_View
WHERE (Student_Id = @user_id) AND ([Sal_Id ] = @Sal_Id)
GROUP BY Dars_Id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TNKS TNKS TNKS

beyk
Starting Member

3 Posts

Posted - 2012-09-11 : 14:56:35

quote:

Originally posted by visakh16


SELECT MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 1 THEN ExamStuInSal_View.Grade END) AS GradeMid1,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 2 THEN ExamStuInSal_View.Grade END) AS GradeTerm1,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 3 THEN ExamStuInSal_View.Grade END) AS GradeMid2,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 4 THEN ExamStuInSal_View.Grade END) AS GradeTerm2
FROM ExamStuInSal_View
WHERE (Student_Id = @user_id) AND ([Sal_Id ] = @Sal_Id)
GROUP BY Dars_Id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

but when i want add other column (dars_id,less_name,num_field,) Error is repeated again!! may you some explain

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-11 : 15:25:10

quote:
Originally posted by beyk

quote:
Originally posted by visakh16

SELECT MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 1 THEN ExamStuInSal_View.Grade END) AS GradeMid1,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 2 THEN ExamStuInSal_View.Grade END) AS GradeTerm1,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 3 THEN ExamStuInSal_View.Grade END) AS GradeMid2,
MAX(CASE WHEN ExamStuInSal_View.Exam_Type_Id = 4 THEN ExamStuInSal_View.Grade END) AS GradeTerm2
FROM ExamStuInSal_View
WHERE (Student_Id = @user_id) AND ([Sal_Id ] = @Sal_Id)
GROUP BY Dars_Id

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

but when i want add other column (dars_id,less_name,num_field,) Error is repeated again!! may you some explain

you're grouping on Dars_Id so you wont be able to select other columns unless you apply aggregation over them.

If you can show sample data and explain what exactly you're trying to achieve I may be able to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources