语法误差接近和)

Question

这是我的查询，但在最后行返回错误"(和第3行，"

"

select (present/total)*100 from( 
    (select count(Staff_Att_Status) from vu_StaffInfo where Staff_Att_Status='Present' group by DHIS_Code) as present,
    (select count(Staff_Att_Status) from vu_StaffInfo group by DHIS_Code) as total
    ) as "result"

Answer 1

select (present/total)* 100.00   --<-- Also use decimal here to get % values like 0.01 
FROM( 
    SELECT   --<-- You need a select key word here 
          (select count(Staff_Att_Status) from vu_StaffInfo where Staff_Att_Status='Present' group by DHIS_Code) as present,
          (select count(Staff_Att_Status) from vu_StaffInfo group by DHIS_Code) as total
    ) as "result"

Answer 2

尝试这个..

select (present/total)*100 "result" from 
    (select count(Staff_Att_Status) from vu_StaffInfo where Staff_Att_Status='Present' group by DHIS_Code) as present,
    (select count(Staff_Att_Status) from vu_StaffInfo group by DHIS_Code) as total

您正在尝试将无法用作内部别名的外部查询混音。

Answer 3

您可能正在寻找类似的东西：(我将您的子征服更改为CTES，因为它们会导致非常易于阅读和理解语法(

with present
as (
  select DHIS_Code, count(*) as count 
  from vu_StaffInfo where Staff_Att_Status='Present' 
  group by DHIS_Code
),
with total
as (
  select DHIS_Code, count(*) as count 
  from vu_StaffInfo 
  group by DHIS_Code
)
select total.DHIS_Code, (isnull(present.count,0) / total.count) * 100.00
from total
     left outer join present on present.DHIS_Code = total.DHIS_Code

您要返回每个dhis_code的百分比，看到您正在按照它进行分组。

我也通过计数(*(更改了计数(staff_att_status(，因为您需要在这些数据集上的行计数，而不是不同的staff_att_status计数。例如，在呈现数据集中，这将始终为1，因为您只有"现在的"状态。