June 6, 2005 at 2:24 pm
DECLARE @university_id varchar(8),@college_id varchar(8) ,@department_id varchar(8) , @year_id tinyint,@semister_id tinyint
SET @university_id='XYZ'
SET @college_id='abc'
SET @department_id='physics'
SET @year_id=4
SET @semister_id=14
SELECT university_id,college_id,department_id,year_id,semister_id,'grade'=
CASE WHEN score <.5 then 'c'
WHEN score>=.5 AND score< .7 THEN 'b'
ELSE 'a'
END,
COUNT(student_id) as number_of_students
FROM university_grades
WHERE university_id=@university_id AND college_id=@college_id AND department_id=@department_id AND year_id=@year_id
GROUP BY university_id,college_id,department_id,year_id,semister_id
I have a database table where I have scores of all the students of a college in the database. I wanted to calculate the report for the student's grades in every department.
Suppose if a department has 100 students and if some student gets a score which is <.5 and they should be awardes a grade 'c' and if they get a score>=.5 AND score< .7 they shoule be awarded a grade 'b' else they should be awarded grade 'a'. I should run this report for all the semisterrs.
WHEN I use the above sql stmt Its giving the following error.
Column 'university_grades.score' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I need the following result set.
university_id,college_id,department_id,year_id,semister_id,grade,number_of_students.
Any help would be deeply appreciated.
June 6, 2005 at 3:03 pm
SELECT university_id,college_id,department_id,year_id,semister_id, grade, count(student_id) as number_of_students
FROM
(
SELECT university_id,college_id,department_id,year_id,semister_id,'grade'=
CASE WHEN score <.5 then 'c'
WHEN score>=.5 AND score< .7 THEN 'b'
ELSE 'a'
END,
student_id
FROM university_grades
WHERE university_id=@university_id AND college_id=@college_id AND department_id=@department_id AND year_id=@year_id
) A
GROUP BY university_id,college_id,department_id,year_id,semister_id, grade
Regards,
gova
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply