x

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?

more ▼

asked May 16, 2012 at 04:16 AM in Default

avatar image

essence388
21 11 11 14

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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.

more ▼

answered May 16, 2012 at 09:41 AM

avatar image

Mrs_Fatherjack
5.2k 66 69 77

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x780
x450
x9

asked: May 16, 2012 at 04:16 AM

Seen: 1265 times

Last Updated: May 16, 2012 at 10:18 AM

Copyright 2017 Redgate Software. Privacy Policy