Grouping Data

Hi Exports

I have a simple question, but I can't seem to get around it. Please help.

I have a table like this: [select * from Enrollment]
Course ID PersonID Role
BCC110 123 Student
BCC110 321 Student
BCC110 456 teacher
BCC123 457 Student

and I want to have a report like

Course ID Total Students Total Teachers
BCC110 2 1
BCC123 1 0

How do I achieve this?

Sam

I've answered a similar question here|||

Hi Henock,

Thanks for your reply and I didn't have a chance to test it out over the weekend. I just tried and I found two things that I couldn't figure out. Please help

This is what I did:
select * from Enrollment e
JOIN
(select coursecode, count(personid) as 'totalStudent' from enrollment where role='Student' group by coursecode) as e2
on e.coursecode = e2.coursecode
JOIN
(select coursecode, count(personid) as 'totalTeachers' from enrollment where role='Teacher' group by coursecode) as e3
on e.coursecode = e3.coursecode

The result is duplicated like there are 10 rows of course 'BCC110' and in each row, it does give me the total students and teachers. However I want to just show the course code once. Second, if there is a course with no teacher, the course won't show.

Please help

*********** Resolved ************************

Here is my solution:

select distinct(e.coursecode), e2.totalstudent, e3.totalteachers from Enrollment e

LEFT outer JOIN

(select coursecode, count(personid) as 'totalStudent' from enrollment where role='Student' group by coursecode) as e2

on e.coursecode = e2.coursecode

LEFT OUTER JOIN

(select coursecode, count(personid) as 'totalTeachers' from enrollment where role='Teacher' group by coursecode) as e3

on e.coursecode = e3.coursecode

Thanks for your help

|||

This will be more efficient, as it should only 1 table read instead of 3:

select e.CourseCode,
sum(case when role ='Student' then 1 else 0 end) as TotalStudent,
sum(case when role ='Teacher' then 1 else 0 end) as TotalTeachers
group by e.CourseCode

|||

Thanks for the reply. Your is definitely a better solution. Unfortunately, I couldn't apply that to my application due to the complexity of our table structure. The example I showed here is a simplied version. We are also collecting information from another table. Anyway, it may be just the limitation of my knowledge. I couldn't add more data from another table using your method. Therefore, I will stick with the old way. But I learned something new for sure......

Cheers!

|||

Feel free to post more info and someone can definitely help. Although because it's a sql question, it might be better to put it in that type of forum. Include a link here that would link us to the new post.

sql