CODEWITHSUNDEEP
mysqlsql
JCrombie
JCrombie

Reputation: 3

How to use AVG(field) in select statement

The question I'm trying to solve is:

List all staff whose salary is less than the average salary and list by how much.

The Tables in questions:

CREATE TABLE Staff
    (`staffNo` varchar(4), `fName` varchar(5), `lName` varchar(5), 
     `position` varchar(10), `sex` varchar(1), `DOB` varchar(9), 
     `salary` int, `branchNo` varchar(4))
;   

INSERT INTO Staff
    (`staffNo`, `fName`, `lName`, `position`, `sex`, `DOB`, `salary`, `branchNo`)
VALUES
    ('SL21', 'John', 'White', 'Manager', 'M', '1-Oct-45', 30000, 'B005'),
    ('SG37', 'Ann', 'Beech', 'Assistant', 'F', '10-Nov-60', 12000, 'B003'),
    ('SG14', 'David', 'Ford', 'Supervisor', 'M', '24-Mar-58', 18000, 'B003'),
    ('SA9', 'Mary', 'Howe', 'Assistant', 'F', '19-Feb-70', 9000, 'B007'),
    ('SG5', 'Susan', 'Brand', 'Manager', 'F', '3-Jun-40', 24000, 'B003'),
    ('SL41', 'Julie', 'Lee', 'Assistant', 'F', '13-Jun-65', 9000, 'B005')
;

The solution I have been working on is:

SELECT s.staffNo, s.fName, s.lName, s.salary
FROM Staff s
WHERE s.salary < (SELECT AVG(s.salary) FROM *)
ORDER BY s.salary DESC;

This works however it doesn't answer the final section of the question, "...and list by how much."

I had though to incorporate it like this:

SELECT staffNo, fName, lName, salary, (30000-salary) as Average
FROM Staff
WHERE salary < (SELECT AVG(salary) FROM Staff)
ORDER BY salary DESC;

But that only gives an error....

Any help you can offer here?

Upvotes: 0

Views: 64

Answers (1)

Barmar
Barmar

Reputation: 780798

Join with a subquery that returns the average salary

SELECT staffNo, fName, lName, salary, avgSalary-salary AS salaryDiff, avgSalary
FROM Staff
JOIN (SELECT AVG(salary) AS avgSalary
      FROM Staff) AS av
ON salary < avgSalary

DEMO

Upvotes: 2

Related Questions