在这种情况下如何修复"哪里"条款？

Question

代码如下:

select
S.Employee_ID,
((S.Salary / 30) * (count(A.Att_check)) + S.Bonus - S.Loan) as [TotalSalary] 
from Salary as S, Attendance as A
where count(A.Att_check) in (
select count(A.Att_check)
from Attendance as A
where A.Att_check = 'attend'
group by A.Employee_ID
)
and A.Employee_ID = S.Employee_ID

错误如下:

聚合不能出现在WHERE子句中，除非它出现在HAVING子句中的子查询或选择列表中，并且被聚合的列是外部引用。

放在第4行

Answer 1

嗯，我不能尝试你的查询，但是，但是从你得到的错误，你可能想试试这个:

改变:

where count(A.Att_check) in ..

:

having count(A.Att_check) in ..

Answer 2

添加以下GROUP BY:

GROUP BY S.Employee_ID , S.Salary , S.BONUS , s.LOAN

SELECT      S.Employee_ID,
((S.Salary/30) * (COUNT(A.Att_check)) + S.Bonus-S.Loan) as [TotalSalary] 
FROM        Salary as S, 
Attendance as A
WHERE       1=1
--AND       COUNT(A.Att_check) 
IN (
SELECT    COUNT(A.Att_check) 
FROM      Attendance as A 
WHERE     A.Att_check = 'attend' 
GROUP BY  A.Employee_ID
)
AND         A.Employee_ID = S.Employee_ID
GROUP BY    S.Employee_ID, 
S.Salary, 
S.BONUS, 
S.LOAN