sql join with two tables with avg

Question

i have two tables employee :

employee_number employee_name salary divison_id

division :

division_id division_name

How to Show employee number, employee name, division_name and average salary of all employees in each division.

question from:https://stackoverflow.com/questions/65942439/sql-join-with-two-tables-with-avg

Answer 1

You can try:

SELECT employee.employee_number, employee.employee_name, division.division_name, 
    AVG(salary) AS AvgSalary
FROM employee
JOIN division ON division.division_id = employee.division_id
GROUP BY employee_number, employee_name, division_name

However, I'm not certain this will achieve your desired result.

To show the "average salary of all employees in each division", you'd do something such as:

SELECT division.division_name AS Division, AVG(salary) AS AvgSalary FROM division
JOIN employee ON employee.division_id = division.division_id
GROUP BY division_name