Problematic Sql Query... - 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 2005 Forums

Transact-SQL (2005)

Problematic Sql Query...

Author

Topic

vijayrawatsan
Starting Member

18 Posts

Posted - 2009-10-15 : 10:57:27

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-15 : 11:00:46

can you post your expected output format? the above queries wont consider Attendance='false' records as you're filtering on true condition

vijayrawatsan
Starting Member

18 Posts

Posted - 2009-10-15 : 11:07:37

quote:
Originally posted by visakh16

can you post your expected output format? the above queries wont consider Attendance='false' records as you're filtering on true condition

Thank You for quick reply.

Count(date)
3
2
2
1
0

Here the row values (i.e. 2 and 1) are the number of times a student is present...(Assuming number of students=5 and maximum attendace=3, minimum attendance=0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-15 : 11:10:51

seems like this


SELECT count(case when a.Attendance='true' then date else null end)
from att a Inner Join students s
On a.student_id=s.student_id
Where a.subject_id=@sid
And a.course_id=@cid
group by s.class_roll

vijayrawatsan
Starting Member

18 Posts

Posted - 2009-10-15 : 11:25:26

quote:

Originally posted by visakh16

seems like this


SELECT count(case when a.Attendance='true' then date else null end)
from att a Inner Join students s
On a.student_id=s.student_id
Where a.subject_id=@sid
And a.course_id=@cid
group by s.class_roll

Thanx a lot vikas it worked....
This forum is awesome....
Got my answer in less than 30 min...
Thanks All.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-15 : 11:32:43

welcome

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-16 : 04:13:54

Another way

SELECT sum(case when a.Attendance='true' then 1 else 0 end)
from att a Inner Join students s
On a.student_id=s.student_id
Where a.subject_id=@sid
And a.course_id=@cid
group by s.class_roll

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources