SQL Server Performance Forum – Threads Archive
Help with this query
I have 2 TablesStudentMaster
StudentDetailMaster In Student Master i have a field called Student Status and it has 3 options
1. Promoted 2.Detained 3.Left School. So i can use a group by query on Status field and Class which they study to get no of students promoted Detained and left school by grouping them on Class. So it looks like
Classname Promoted Detained Left School
10 Grade 10 20 30 But my requirment was to split the Detained into 2 section.
In StudentsDetailMaster there is one field called Process and i have to get detained list which are processed and which are not processed based on that field in another table.
So it looks like
Classname promoted detailed_processed Detailed_unProcessed LeftSchool Need help with this query
thanks and regards
I would suggesting trying a subquery based on studentdetailmaster to give you the detailed_processed and detailed_unprocessed figures – alternatively I think a self join would let you do this as well
Petew
or Post some sample data and the result you want Madhivanan Failing to plan is Planning to fail
Thanks Madhivanan and Trabant Sample Data Student Master table
StudentCode StudentClassCode StudentStatus
1 10 Pass
2 10 Failed
3 10 LEFT
4 11 PASS
5 10 FAILED Student Details table
StudentCode Processstatus
2 Yes
5 No
So i want the results like this
ClassCode Noofstudentspassed NoofstudentsLEft NOofstudentsfailed(processstatus =yes) Noofstudentsfailed(process status=no)
10 1 1 1 1
11 1 0 0 0 Thanks for the reply in advance
Refer Cross-Tab Reports in sql server help file Madhivanan Failing to plan is Planning to fail
select
m.ClassCode,
sum(case (m.StudentStatus)
when ‘Pass’ then 1
else 0
end) as NoOfStudentsPassed,
…
from StudentMaster m
left join StudentDetails d on m.StudentCode = d.StudentCode
group by m.ClassCode
From your sample it is not clear does every master row have corresponding detail row.
Yes for every student master record there will be record in studentdetails. So basically what i want is to get the count of students failed as
failed(processed) failed(notprocessed) ClassCode noofstudentspassed no ofstudentsfailed(processed) noofstudentsafiled(unprocessed) for fail processed detail status field will be 1
for fail unprocessed details statuf field will be 2
for passed students details status field will be 0 so you have corresponding students details records for every student master and relation is one to many between
student master and studentdetails. so we have to find out is there any row in details table that is there a record which has value either 1 or 2 (only one will be there) (and all failed records have this records there) and if it is 1 then failedprocessed if 2 failed unprocessed.
Use the method I posted, just use inner instead of outer join. More explanations are in BOL as Madhivanan already suggested.
select m.ClassCode, sum(case (m.StudentStatus) when ‘Pass’ then 1 else 0 end) as NoOfStudentsPassed,
sum((M.studentstatus) when d.studentstatus=1 and ‘FAIL’ then 1 else 0 END) as FailedProcessed,
sum((M.studentstatus) when d.studentstatus=2 and ‘FAIL’ then 1 else 0 END) as FailedUnprocessed,
…from StudentMaster mleft join StudentDetails d on m.StudentCode = d.StudentCodegroup by m.ClassCode I need a query more or less like i have put here.I couldnt able to make this query work and could you able to correct the query for me. I looked at cross tab reports and it has only one table refered and so didnt able to find out how it can be implemented comparing 2 tables. Thanks and Regards
You don’t need left join. I used left join because I didn’t know if there is master row without corresponding detail row. Your syntax is wrong you can’t mix two case syntaxes.
select m.ClassCode,
sum(case (m.StudentStatus) when ‘Pass’ then 1 else 0 end) as NoOfStudentsPassed,
sum(case when d.studentstatus=1 and m.studentStatus = ‘FAIL’ then 1 else 0 END) as FailedProcessed,
…
from StudentMaster m
join StudentDetails d on m.StudentCode = d.StudentCode
group by m.ClassCode
It doesn’t matter how many tables are joined, just use appropriate table alias in front of column name.
Thank you mmarovic.that worked.
]]>