sqlselect

SQL query to select average salary for each employee

When I tried to select employee fname,lname, salary and the average of the salary it returned the same number for all employee. here what I typed to query:

SELECT 
    Fname AS EmployeeFirstName, 
    Lname AS EmployeeLastName, 
    Salary AS Sal, 
    (SELECT AVG(Salary) FROM Employee) AS AverageSalary,
FROM 
    Employee;

And this is the screenshot of the result: enter image description here

I want the average be counted for each employee salary, any idea?

Solution

  • You shall use GROUP BY clause.

    SELECT 
    Fname AS EmployeeFirstName, 
    Lname AS EmployeeLastName, 
    AVG(Salary) AS Sal
FROM  Employee
GROUP BY employeeId; -- Replace this by the column id specific to the employees

    Note: You'll need to set sql_mode to "only_full_group_by" or "" (empty).

    For instance, you might do:

    SET sql_mode = '';

    See this answer for more information