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.
| Author |
Topic |
|
Zath
Constraint Violating Yak Guru
298 Posts |
 Posted - 2007-02-23 : 13:03:50
|
| Ok, going to take this one step at a time.Here's the sql and working in test tables for now: Select a.courseId, count(a.courseId) as [count], avg(convert(INT, a.fldScore)) as [average], count(fldPass) as [passed], count(fldPass) as [failed] From tblTest a Group by courseId Above, there are 2 return params, passed and failed.I need to select these as something like:count(fldPass) as [passed] where fldPass = 'yes'Also, I need to join another table here, tblTest2 b and get b.courseName where a.courseId = b.courseIdSuggestions?Thanks,Zath |
|
|
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-02-23 : 14:00:32
|
| Ok, got a little further on this one: Select b.courseName, a.courseId, count(a.courseId) as [count], avg(convert(INT, a.fldScore)) as [average], count(fldPass) as [passed], count(fldPass) as [failed] From tblTest a inner join tblTest2 b on a.courseId = b.courseId Group by a.courseId, b.courseNameProblem is the [passed] and [failed]As it is, it's counting all of them.I need to adjust it so passed will only read where fldPass = 'yes'and fldPass = 'no' for the passed and failed.Suggestions?Thanks,Zath |
 |
|
|
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-02-23 : 14:29:48
|
| Ok, got it working: Select b.courseName, a.courseId, count(a.courseId) as [count], avg(convert(INT, a.fldScore)) as [average], count(CASE fldPass WHEN 'yes' THEN 1 ELSE NULL END) as [passed], count(CASE fldPass WHEN 'no' THEN 1 ELSE NULL END) as [failed] From tblTest a inner join tblTest2 b on a.courseId = b.courseId Group by a.courseId, b.courseName WoooHooo.......Zath |
|
|
|
Zath
Constraint Violating Yak Guru
298 Posts |
Posted - 2007-02-23 : 14:29:55
|
| Ok, got it working: Select b.courseName, a.courseId, count(a.courseId) as [count], avg(convert(INT, a.fldScore)) as [average], count(CASE fldPass WHEN 'yes' THEN 1 ELSE NULL END) as [passed], count(CASE fldPass WHEN 'no' THEN 1 ELSE NULL END) as [failed] From tblTest a inner join tblTest2 b on a.courseId = b.courseId Group by a.courseId, b.courseName WoooHooo.......Zath |
|
|
|
|
|
|
|
|
count where fld has certain value