|
Hello All, I am trying to write a query to count Attendance(or Present) of a student using its student id. Below is the scenario: str=string Table Att Student_id(str),Course_id(str),Subject_id(str),Dat e(datetime),Attendance(str) Tabel Students Student_id(str),Name(str),Class_roll(str) Now the queries I have tried uptill are:: SELECT count(date) from att a Inner Join students s On a.student_id=s.student_id Where a.subject_id=@sid And a.course_id=@cid And a.Attendance='true' group by s.class_roll SELECT ISNULL(count(date),0) from att a Inner Join students s On a.student_id=s.student_id Where a.subject_id=@sid And a.course_id=@cid And a.Attendance='true' group by s.class_roll SELECT COALESCE(count(date),0) from att a Inner Join students s On a.student_id=s.student_id Where a.subject_id=@sid And a.course_id=@cid And a.Attendance='true' group by s.class_roll SELECT ISNULL(count(date),0) from att a Left Outer Join students s On a.student_id=s.student_id Where a.subject_id=@sid And a.course_id=@cid And a.Attendance='true' group by s.class_roll SELECT ISNULL(count(date),0) from att a Right Outer Join students s On a.student_id=s.student_id Where a.subject_id=@sid And a.course_id=@cid And a.Attendance='true' group by s.class_roll The problem here is that query is not returning NULL for the cases where a.attendance='false' and the count for that tuple is not calculated..... So I am getting less number of tuples. I want 0 when a.attendance='false'. I hope some one could solve my problem...... Thanks.
(comments are locked)
|
|
Try this:
SELECT sum(case when a.attendance = 'true' then 1 else 0 end) as att_count
from att a
inner Join students s
On a.student_id=s.student_id
Where a.subject_id=@sid
And a.course_id=@cid
--And a.Attendance='true'
group by s.class_roll
TG, you dont need 0 if you use COUNT
Oct 16 '09 at 04:43 AM
Madhivanan
woops - of course you're right - thanks. I'll changed it.
Oct 16 '09 at 12:09 PM
TG
(comments are locked)
|
