How to fix "where" clause in this situation?-CodePudding

Here is the code:

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

And here is the error:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

It places on the 4th line from the top

CodePudding user response：

Well, I can't try your query but, but from the error you are getting, you might want to try this:

change:

where count(A.Att_check) in ..

to:

having count(A.Att_check) in ..

CodePudding user response：

Add The following 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