Count problem
-
Hi I have a table named StudentResult UserID School Semester Grade Campus Status 1 School1 Summer 10 Campus1 Accepted 2 School1 Summer 10 Campus1 Accepted 3 School1 Summer 10 Campus1 Rejected 4 School1 Summer 10 Campus1 Rejected 5 School1 FALL 10 Campus1 Accepted 6 School1 Summer 12 Campus1 Accepted 7 School1 Summer 12 Campus1 Rejected 8 School1 Summer 12 Campus1 Accepted 9 School1 Summer 12 Campus1 Rejected 10 School1 Summer 12 Campus1 Accepted 11 School1 FALL 12 Campus1 Rejected 12 School1 FALL 12 Campus1 Accepted 13 School1 FALL 11 Campus1 Rejected 14 School1 FALL 11 Campus1 Accepted 15 School1 FALL 11 Campus1 Accepted 16 School1 Summer 11 Campus1 Rejected 17 School1 Summer 11 Campus1 Accepted 18 School1 Summer 11 Campus1 Accepted I want to take the number of accepted and rejected users from the above table as the result table below.I want to consider grade,Campus and semester for taking count and the result should be like result table.Please help me to write Query for this. I am using SQL server2000. as database School Semester Grade Campus NoofAccepted NoofRejected School1 Summer 10 Campus1 2 2 School1 Fall 10 Campus1 1 0 School1 Summer 11 Campus1 2 1 School1 Fall 11 Campus1 2 1 School1 Summer 12 Campus1 3 2 School1 Fall 12 Campus1 1 1 Thanx
-
Hi I have a table named StudentResult UserID School Semester Grade Campus Status 1 School1 Summer 10 Campus1 Accepted 2 School1 Summer 10 Campus1 Accepted 3 School1 Summer 10 Campus1 Rejected 4 School1 Summer 10 Campus1 Rejected 5 School1 FALL 10 Campus1 Accepted 6 School1 Summer 12 Campus1 Accepted 7 School1 Summer 12 Campus1 Rejected 8 School1 Summer 12 Campus1 Accepted 9 School1 Summer 12 Campus1 Rejected 10 School1 Summer 12 Campus1 Accepted 11 School1 FALL 12 Campus1 Rejected 12 School1 FALL 12 Campus1 Accepted 13 School1 FALL 11 Campus1 Rejected 14 School1 FALL 11 Campus1 Accepted 15 School1 FALL 11 Campus1 Accepted 16 School1 Summer 11 Campus1 Rejected 17 School1 Summer 11 Campus1 Accepted 18 School1 Summer 11 Campus1 Accepted I want to take the number of accepted and rejected users from the above table as the result table below.I want to consider grade,Campus and semester for taking count and the result should be like result table.Please help me to write Query for this. I am using SQL server2000. as database School Semester Grade Campus NoofAccepted NoofRejected School1 Summer 10 Campus1 2 2 School1 Fall 10 Campus1 1 0 School1 Summer 11 Campus1 2 1 School1 Fall 11 Campus1 2 1 School1 Summer 12 Campus1 3 2 School1 Fall 12 Campus1 1 1 Thanx
select School, Semester, Grade, Campus, sum(CASE WHEN Status = 'Accepted' THEN 1 ELSE 0 END) AS NumOfAccepted, sum(CASE WHEN Status = 'Rejected' THEN 1 ELSE 0 END) AS NumOfRejected from StudentResult group by School, Semester, Grade, Campus Of course, this is what you have asked for, but not necessarily the best way of going about it... you could always just use count (*) and use the Status as a Column heading also...
------------------------------------------- Don't walk in front of me, I may not follow; Don't walk behind me, I may not lead; Just bugger off and leave me alone!!