x

Problematic Sql Query...

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.

more ▼

asked Oct 15, 2009 at 11:56 AM in Default

avatar image

Vijay Rawat
11 1 1 3

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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            
more ▼

answered Oct 15, 2009 at 12:41 PM

avatar image

TG
1.8k 3 5

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2016
x425

asked: Oct 15, 2009 at 11:56 AM

Seen: 1382 times

Last Updated: Oct 16, 2009 at 10:30 AM

Copyright 2016 Redgate Software. Privacy Policy