Ranking an SQL on 2 Levels

Okay, another SQL question. What I am trying to achieve is to show by company, which courses their employees have been to, and from there, which is the most pop开发者_StackOverflow中文版ular for each company.

Currently, this is what I have:

SELECT a.corporatename, e.course_title, b.studentID FROM corporate a

JOIN students b ON a.corporateID = b.corporateID

JOIN studentcoursedetails c ON b.studentID = c.studentID

JOIN classdetails d ON c.classdetailID = d.classdetailID

JOIN course e ON d.courseID = e.courseID

WHERE b.corporateID != '0'

ORDER BY a.corporatename, e.course_title

This gives me something along these lines:

corporatename-----------------------------course_title---------------------------------studentID

Deutsche Lufthansa AG Berlin-----Airline Security Operations---------5

Deutsche Lufthansa AG Berlin-----Airline Security Operations---------6

Deutsche Lufthansa AG Berlin-----Airline Security Operations---------5

Deutsche Lufthansa AG Berlin-----Workshop on Crisis Management------6

Deutsche Lufthansa AG Berlin-----Workshop on Crisis Management------7

Korean Air Lines Co., Ltd----------Airline Security Operations---------8

From the above, we can see that Lufthansa has sent it's employees to 2 courses so far. Even though studentID 5 has been to a particular course twice, because he flunked the first try, it is recorded as 2 entries.

What I hope to attain is this:

Deutsche Lufthansa AG Berlin-----Airline Security Operations---------3

Deutsche Lufthansa AG Berlin-----Workshop on Crisis Management------2

Korean Air Lines Co., Ltd----------Airline Security Operations---------1

I tried to use this code:

SELECT a.corporatename, e.course_title, count( DISTINCT b.studentID ) AS total

FROM corporate a

JOIN students b ON a.corporateID = b.corporateID

JOIN studentcoursedetails c ON b.studentID = c.studentID

JOIN classdetails d ON c.classdetailID = d.classdetailID

JOIN course e ON d.courseID = e.courseID

WHERE b.corporateID != '0'

GROUP BY a.corporatename

ORDER BY a.corporatename, e.course_title

But what I got:

corporatename-----------------------------course_title---------------------------------total

Deutsche Lufthansa AG Berlin-----Workshop on Crisis Management------3

Korean Air Lines Co., Ltd----------Airline Security Operations---------1

it shows me the most popular, yes, but I'd like to be able to compare all ranks.

I'd appreciate any help on this.

Thanks to bluish, a simple solution.

SELECT a.corporatename, e.course_title, count( b.studentID ) AS total

FROM corporate a

JOIN students b ON a.corporateID = b.corporateID

JOIN studentcoursedetails c ON b.studentID = c.studentID

JOIN classdetails d ON c.classdetailID = d.classdetailID

JOIN course e ON d.courseID = e.courseID

WHERE b.corporateID != '0'

GROUP BY a.corporatename , e.course_title

ORDER BY a.corporatename, e.course_title

I removed the DISTINCT because I need to collect data of all enrollments, not each unique user. Thanks.

Try to add also e.course_title in the GROUP BY:

SELECT a.corporatename, e.course_title, count( DISTINCT b.studentID ) AS total
FROM corporate a
JOIN students b ON a.corporateID = b.corporateID
JOIN studentcoursedetails c ON b.studentID = c.studentID
JOIN classdetails d ON c.classdetailID = d.classdetailID
JOIN course e ON d.courseID = e.courseID
WHERE b.corporateID != '0'
GROUP BY a.corporatename, e.course_title
ORDER BY a.corporatename, e.course_title