sql count with inner join

Question

Post reply

sql count with inner join

shanish

Right there with Babe

Points: 768
More actions
April 12, 2012 at 12:52 am

#253901

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

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

mrashish_jaiswal Valued Member Points: 61 More actions · Answer 1

mrashish_jaiswal

Valued Member

Points: 61

April 12, 2012 at 1:28 am

#1472405

Can you pls elaborate other table structure 🙂

shanish Right there with Babe Points: 768 More actions · Answer 2

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

CapnHector SSCoach Points: 16143 More actions · Answer 3

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 faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

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]

surma.sql SSCommitted Points: 1552 More actions · Answer 4

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