asked
How to show the participation percentage in all departments?
I have the following database design: Employees Table: EmployeeID, Name, OrgCode Departments Table: OrgCode, DepartName CompleteSurvey Table: ID, ParticipantID And I need to develop one query that will display a table that shows the total number of employees in all departments and the total number of participants who completed the survey in all departments. I could be able to find the total number of employees in all department by the following query: SELECT COUNT(DISTINCT dbo.Employees.EmployeeID) AS [Total Number of Employees] FROM dbo.Departments INNER JOIN dbo.Employees ON dbo.Departments.OrgCode = dbo.Employees.OrgCode CROSS JOIN dbo.CompleteSurvey Then, I could be able to find the total number of participants in all department by the following query: SELECT COUNT(DISTINCT
dbo.CompleteSurvey.ID) AS [Total Number of Participants] FROM dbo.Departments INNER JOIN dbo.Employees ON dbo.Departments.OrgCode = dbo.Employees.OrgCode INNER JOIN dbo.CompleteSurvey ON dbo.Employees.EmployeeID = dbo.CompleteSurvey.RespondantID But I should have a one query only. For example, if Department A has 100 employee and the number of participants is 50 out of 100 and Department B has 80 employee and the number of participants is 30 The query should show the following: **the total number of employees in all departments = 180** **the total number of participants in all departments = 80** **the percent completion in all of them = 80/180 = 44%** **So how to do that?**
This should do it: SELECT COUNT(*) AS [NumberEmployeesInAllDepartments], COUNT(CS.ParticipantID ) AS [NumberParticipants], (CAST(CAST(COUNT(CS.ParticipantID )AS decimal(9,2)) / cast(COUNT(*) as decimal(9,2)) AS DECIMAL(9,2)) ) * 100 AS [PercentageCompleted] FROM Departments AS D JOIN Employees AS E ON D.OrgCode = E.OrgCode LEFT OUTER JOIN CompleteSurvey AS CS ON E.EmployeeID = CS.ParticipantID I have found that you need to keep the cast to decimal in otherwise it returns 0.
4 People are following this question.
