在我的SQL Server查询中,我试图计算每个站点的员工人数。这可行,但是当我尝试添加总计的百分比时,它仍然按网站分组,因此不准确。
有没有更简单的方法来实现这一目标?
我正在使用此查询来创建视图。
select Site.SiteName,
sum(case when Employee.ActiveStatus = 'Yes' then 1 else 0 end) as
"NumberOfEmployees",
CONVERT(decimal(6,2),(sum(case when Employee.ActiveStatus = 'Yes' then 1
else 0 end))/(convert(decimal(6,2),COUNT(EmployeeID)))) as PercentageOfEmps
from Employee
left join Site
on(Employee.SiteID=Site.SiteID)
GROUP BY Site.SiteName;
GO
您可以使用子查询:
select
Site.SiteName,
NumberOfEmployees = sum(case when Employee.ActiveStatus = 'Yes' then 1 else 0 end),
PercentageOfEmps = CONVERT(decimal(6,2),(sum(case when Employee.ActiveStatus = 'Yes' then 1
else 0 end))/(SELECT COUNT(EmployeeID) FROM Employee)
from Employee
left join Site
on Employee.SiteID=Site.SiteID
GROUP BY Site.SiteName;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句