如何显示每个部门的参与率？

admin

如何显示每个部门的参与率？

sql

我有以下数据库设计：

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

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

我提出了以下两个查询，但是我必须提出一个查询来满足上述要求， 那么该怎么做呢？

每个部门的员工总数：

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

每个部门的参与者总数：

 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

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

更新：

基本上，我想要的是一个查询，该查询显示每个部门的雇员总数以及每个部门中完成调查的参与者总数，并显示参与百分比。

阅读 181

2021-07-01

共1个答案

admin

如果我正确理解当雇员提交调查时CompleteSurvey充满EmployeeID，则此查询将检索您所需的信息。您可以检查实时测试@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

如果需要所有部门，请使用以下查询：

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

2021-07-01