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

Vijay Rawat gravatar image

Vijay Rawat
11 1 1 1

(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

TG gravatar image

TG
1.8k 1 3

TG, you dont need 0 if you use COUNT
I think it sould be SUM otherwise
Oct 16, 2009 at 04:43 AM Madhivanan
woops - of course you're right - thanks. I'll changed it.
Oct 16, 2009 at 12:09 PM TG
(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:

x1951
x376

asked: Oct 15, 2009 at 11:56 AM

Seen: 1266 times

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