How do I compare using the AVG function in SQL?

Solution for How do I compare using the AVG function in SQL?
is Given Below:

I have two tables, One for Workers and One for Departments.

Worker(WorkerID, DeptID, Name, Age, Experience)
Department(DeptID, Name)

I want to find names of Departments that have workers with Average Experience under 5 years.

The query I wrote is,

SELECT DISTINCT Department.Name AS DeptName
FROM Department
LEFT OUTER JOIN Worker ON Worker.DeptID = Department.DeptID
WHERE 5 > ( SELECT AVG (Experience)  FROM Workers);

This doesn’t give me any output or errors. What am I doing wrong?

If i put 5 in quotes as “5”, it returns all Departments instead of only those with Avg Experience under 5 years.

What’s wrong with your query

SELECT DISTINCT Department.Name AS DeptName
FROM Department
LEFT OUTER JOIN Worker ON Worker.DeptID = Department.DeptID
WHERE 5 > ( SELECT AVG (Experience)  FROM Workers);

A. You calculate average experience of all workers

 ( SELECT AVG (Experience)  FROM Workers)

And you get a value.

This value could greater or less then 5.

B. You compare calculated value with 5

WHERE 5 > ( SELECT AVG (Experience)  FROM Workers);

This predicate will be always False or always True.

This doesn’t give me any output or errors.

So effectively it becames WHERE False for all rows.

It’s not an error but you get 0 rows for that.

What I suppose is needed

Notes:

  • “I suppose” – in general it advised to prepare a db fiddle for question.
  • It’s better to specify mysql version

You wrote:

I want to find names of Departments that have workers with Average Experience under 5 years.

Average Experience by department?

Do you need:

SELECT Department.Name AS DeptName
FROM Department
LEFT OUTER JOIN Worker ON Worker.DeptID = Department.DeptID
GROUP BY Department.Name
HAVING AVG(Experience) < 5

?

If you like, you can do this with a subquery in the WHERE clause:

SELECT d.Name AS DeptName
FROM Department d
WHERE (SEELCT AVG(w.Experience)
       FROM Workers w
       WHERE w.DeptID = d.DeptId
      ) < 5;

You can also use GROUP BY and HAVING, as suggested in the other answer. This might be a wee bit faster with an index on Department(DeptId, Experience).

Other approach to get the same result (using GROUP BY and HAVING):

I group by department and filter those departments which workers average experience is less than five years.

SELECT Department.Name AS DeptName
FROM Department
INNER JOIN Worker ON Worker.DeptID = Department.DeptID
GROUP BY Department.Name
HAVING AVG(Experience) < 5;

This query SELECT AVG(Experience) FROM Worker; return the average experience of all workers together, not by deparment.