April 12, 2012 at 12:52 am
I have a table for absentees, in that table am storing the studentids those who are absent. from this table I had to find total presentees and total absentees, for this I just joined the Sections table which contains the maximum capacity of particular Section, for this my query was
select COUNT(Attendance.studentid) as Absentees
,Sections.Max-count(studentid) as Presentees
from Attendance
inner join Students
on students.StudentId=Attendance.StudentId
inner join Sections
on Sections.CourseId=students.CourseId
group by Sections.Max
its working fine, the same way how can I find the gender wise presentees/absentees......gender column is in Students table, can anyone give me some idea, thanks in advance
April 12, 2012 at 1:28 am
Can you pls elaborate other table structure 🙂
April 12, 2012 at 1:30 am
thanks for ur response, I tried like this
select COUNT(Attendance.studentid) as Absentees,
Sections.Max-count(studentid) as Presentees,
Students.Gender as Gender
from Attendance
inner join Students
on Students.StudentId=Attendance.StudentId
inner join Sections
on Sections.CourseId=Students.CourseId
group by Sections.Max, Students.Gender
and its working
April 12, 2012 at 6:49 am
mrashish_jaiswal (4/12/2012)
Can you pls elaborate other table structure 🙂
he is asking for DDL (create table statements) and sample data (hard coded inserts). please see the link in my signature for how we like to see those.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 12, 2012 at 9:33 am
Hey
Try this
May be this one will help
select
Students.Gender as Gender,
COUNT(Attendance.studentid) as Absentees,
max(Sections.studentid) as Presentees,
from Attendance inner join Students
on Students.StudentId=Attendance.StudentId
inner join Sections on Sections.CourseId=Students.CourseId
group by Students.Gender
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply