essence2012388 avatar image
essence2012388 asked

How to show the Percent Completion of each division in each course?

I am developing a web-based application that tracks the training for each employee in the company. I have the following database design: Employee Table: Username, Name, DivisionCode Division Table: SapCode, DivisionName Course Table: CourseID, CourseName, GroupID Group Table: GroupID, GroupName Employee_Course Table: Username, CourseID (The first attribute in each table is the primary key except for the last table) I have **to implement a chart or table that shows the percent completion of each division in each course of these courses**, but I don't know how to come up with this query. The query should show the DivisionName, CourseName, the Number of Participants in each Course and the Percent Completion. ***It should show all courses even if there is no employees participated in that course.*** **So could you please help me with this query?** My query which still needs more improvements to give me what I want is: select a.DivisionName, a.CourseName, a.ParticipantCount, ParticipantCount / EmployeeCount * 100 as PercentCompleted from ( select DivisionName, count(*) as EmployeeCount from Divisions d inner join Employee e on d.SapCode = e.DivisionCode group by DivisionName ) dc INNER JOIN ( select d.DivisionName, c.CourseName, count(*) as ParticipantCount from Courses c inner join Employee_Courses ec on c.CourseID = ec.CourseID inner join Employee e on ec.employeeId = e.Username inner join Divisions d on e.DivisionCode = d.SapCode group by DivisionName, CourseName ) a on dc.DivisionName = a.DivisionName
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
Variations on this question have been asked several times over the last few months, presumably by students studying the same material. Perhaps you could see if one of the previous answers has what you need, so we don't duplicate answers.
0 Likes 0 ·

0 Answers


Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.