CODEWITHSUNDEEP
sqlsql-serverssms
simpati2gb
simpati2gb

Reputation: 17

How can i find salary is more than the average salary of every staff

I want to view rows from my table where the salary is more than the average salary of every staff member. With the code below it it giving me an error:

Operand data type varchar is invalid for avg operator.

SELECT StaffID, StaffName, LEFT(StaffGender, 1)  AS [Staff Gender], 
'Rp. ' + CAST ((StaffSalary)AS VARCHAR) [Staff Salary]
FROM MsStaff
WHERE StaffName Like '% %' AND StaffSalary > AVG(StaffSalary)

Update: I have changed the StaffSalary column to INT datatype, but I get another error:

Msg 147, Level 15, State 1, Line 48
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."

Data sample

Upvotes: 0

Views: 228

Answers (2)

Ravi Sharma
Ravi Sharma

Reputation: 370

select * from MsStaff where StaffSalary > (select avg(StaffSalary) from MsStaff)

Upvotes: 0

Bohemian
Bohemian

Reputation: 424993

Although it seems to makes sense, you can’t use AVG() like that.
Use a sub-query to find the average:

SELECT ...
FROM MsStaff
WHERE StaffName Like '% %'
AND StaffSalary > (SELECT AVG(StaffSalary) FROM MsStaff)

Upvotes: 1

Related Questions