如何显示每个部门的参与百分比? [英] How to show the participation percentage in each department?

问题描述

我有以下数据库设计:

Employees Table: EmployeeID, Name, OrgCode
Departments Table: OrgCode, DepatName
CompleteSurvey Table: ID, ParticipantID

而且我需要开发一个表格来显示每个部门的员工总数，以及每个部门完成调查的参与者总数.然后，我想显示每个部门的参与百分比，主要等于参与者总数/员工总数.

And I need to develop a table that shows the total number of employees in each department, and the total number of participants who completed the survey in each department, too. Then, I want to show the percentage of participation in each department which is mainly equal to the total number of participants / total number of employees.

我想出了以下两个查询，但我必须想出一个显示上述要求的查询，那该怎么做?

I came up with the following two queries but I have to come up with one query that shows the above requirement, so how to do that?

每个部门的员工总数:

SELECT COUNT(DISTINCT dbo.Employees.EmployeeID) AS [Total Number of Employees], 
       dbo.Departments.DepartmentName
FROM dbo.Departments 
INNER JOIN dbo.Employees ON dbo.Departments.OrgCode = dbo.Employees.OrgCode
GROUP BY dbo.Departments.DepartmentName

<小时>

每个部门的参与者总数:

---

Total number of participants in each department:

 SELECT COUNT(DISTINCT dbo.Employees.EmployeeID) AS [Total Number of Employees], 
 dbo.Departments.DepartmentName
 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
 GROUP BY dbo.Departments.DepartmentName

第二个查询显示参与者的问题，即使没有参与者，它也不会显示所有部门，它应该显示零.

The problem with the second query that shows the participants, it doesn't show all the departments even if they have no participants, it should show zeros.

更新:

基本上，我想要的是一个查询，显示每个部门的员工总数和每个部门完成调查的参与者总数，并显示参与百分比.

Basically, what I want is to have one query that shows the total number of employees in each department and the total number of participants who completed the survey in each department, and show the percentage of participation.

推荐答案

如果我理解正确，当员工提交调查时，CompleteSurvey 会填入 EmployeeID，此查询将检索您需要的信息.您可以查看现场测试@Sql Fiddle.

If I understood correctly that CompleteSurvey is filled with EmployeeID when employe submits a survey, this query will retrieve the info you need. You can check live test @ Sql Fiddle.

select Departments.DepatName, 
       count (Employees.EmployeeID) Employees, 
       count (CompleteSurvey.ID) Finished,
       cast (count (CompleteSurvey.ID) as float) / count (Employees.EmployeeID)
            PercentageFinished
  from Departments
 inner join Employees 
    on Departments.OrgCode = Employees.OrgCode
  left join CompleteSurvey 
    on Employees.EmployeeID = CompleteSurvey.ParticipantID
 group by Departments.DepatName

如果您需要所有部门，请使用此查询:

If you need all the departments, use this query:

select Departments.DepatName, 
       count (Employees.EmployeeID) Employees, 
       count (CompleteSurvey.ID) Finished,
       case when count (Employees.EmployeeID) <> 0
            then cast (count (CompleteSurvey.ID) as float) 
                     / count (Employees.EmployeeID)
            else 0
        end PercentageFinished
  from Departments
  left join Employees 
    on Departments.OrgCode = Employees.OrgCode
  left join CompleteSurvey 
    on Employees.EmployeeID = CompleteSurvey.ParticipantID
 group by Departments.DepatName

新测试@Sql Fiddle.

这篇关于如何显示每个部门的参与百分比?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！