Make the query shorter - 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)

Make the query shorter

Author

Topic

abhi123
Starting Member

10 Posts

Posted - 2009-05-28 : 13:26:49

Hi
I am working on the data in which there is a school. In which a student can get grade on 28 different categories for a course. Grades can be either P or F. I want to count the number of P he got for all the courses. The table i have 28 columns for that different categories. so i have to use 28 select statements. This will be big query Because later i have to count the F too. i am thinking like this
Select studentName,(select count(cat1) from Grades where cat1 ='P'),(select count(cat2) from Grades where cat2 ='P'), Grades, student, course where student.id = course.studentId and course.id = grades.courseID

Is there any way to shorter this query
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-05-28 : 13:32:30

Perhaps you can explain what you want to do. I'm pretty sure you can do it without 28 separate queries. The one you posted it not complete (no FROM clause). But I get the gist. This link might help you to articulate you particular issue better so we can offer a better suggestion for you:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Maybe all you need is a CASE statement??

Select 
	studentName,
	SUM(CASE WHEN Grades.cat1 = 'P' THEN 1 ELSE 0 END) AS SumOfGradesCat1Pass,
	SUM(CASE WHEN Grades.cat2 = 'P' THEN 1 ELSE 0 END) AS SumOfGradesCat2Pass,
	Grades, 
	student, 
	course 
FROM
	???
	where student.id = course.studentId and course.id = grades.courseI

abhi123
Starting Member

10 Posts

Posted - 2009-05-28 : 13:40:18

Thanks for your help. i tried your solution it works fine. i will be more clearer next time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources