asked
How to modify this query to show the total number of employees and percent completion in each division?
I have the following database design: Employee Table: Username, Name, DivisionCode Division Table: SapCode, DivisionShortcut Courses Table: CourseID, CourseName, GroupID Groups Table: GroupID, GroupName Employee_Courses Table: EmployeeID, CourseID I came up with the following query that shows the divisions, courses and the total number of participants in each division: SELECT dbo.Divisions.DivisionShortcut, dbo.courses.CourseName, COUNT(dbo.employee_courses.courseId) AS [Total Number of Participants] FROM dbo.courses INNER JOIN dbo.employee_courses ON dbo.courses.CourseID = dbo.employee_courses.courseId INNER JOIN dbo.groups ON dbo.courses.GroupID =
dbo.groups.ID RIGHT OUTER JOIN dbo.employee AS employee_1 INNER JOIN dbo.Divisions ON employee_1.DivisionCode = dbo.Divisions.SapCode ON dbo.employee_courses.employeeId = employee_1.Username WHERE (dbo.courses.GroupID = 1) GROUP BY dbo.courses.CourseID, dbo.courses.CourseName, dbo.Divisions.DivisionShortcut In addition to be shown above, I want to display the total number of employees in each division and the percent completion in each division, too. By the way, the percent completion is equal to ***(total number of participants in each course / total number of employees in each division)*** **So how to do that?**
Not having any example data to play with, this is only a pointer in the right direction, but I would be looking to use [partitioned window functions][1], such as: count(dbo.employee_courses.courseId) over(partition by dbo.Divisions.DivisionShortcut) to get the number of employees in each division. If that is right for you, then you can use that to calculate the percentage. [1]:
http://msdn.microsoft.com/en-us/library/ms189461.aspx
8 People are following this question.
