How can I create this complicated SQL query? (Bad title but well-explained question inside)

Question

This question is a bit difficult to explain so please bear with me. I have two tables in an SQL Database: Departments and Employees, and the tables look like this: (this is a simplified example)

CREATE TABLE Departments
(
DeptID INT NOT NULL,
DeptName VARCHAR(50) NOT NULL,
CONSTRAINT DeptID_pk PRIMARY KEY (DeptID)
);

CREATE TABLE Employees
(
EmpID INT NOT NULL,
EmpAge INT NOT NULL,
DeptID INT NOT NULL,
CONSTRAINT EmpID_pk PRIMARY KEY (EmpID),
CONSTRAINT Employees_DeptID_fk FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
ON UPDATE CASCADE ON DELETE RESTRICT;
);

I need to perform a query that will do the following:

1. For each department
2. Calculate a pseudo salary for each employee in the department based on the formula EmpAge*100
3. Add up all the pseudo salaries to get the total salary paid out to the department
4. Divide the total salary for the department by the total number of employees in the department
5. Display the DeptID, DeptName, and 'Average Salary of employees', which is steps 2-4.

If this was in pseudocode, I would try to do something like this:

for dept in departments:
    dept_salary = 0
    dept_num_employees = 0
    for employee in dept:
        dept_salary += employee.Age*100
        dept_num_employees += 1
    average_salary = dept_salary / dept_num_employees
    print(dept.ID, dept.name, average_salary)

I have tried using SUM, AVG, and COUNT commands in MySQL, but I just can't get the expected output. How can this query be successfully executed in MySQL?

Here is some sample data if you want it:

use company;
insert into company.Department
values
(101, 'Marketing'),
(102, 'Finance'),
(103, 'Operations');

use company;
insert into company.Employees
values
(1001,28,101), 
(1002,30,102), 
(1003,21,103), 
(1004,20,103), 
(1005,23,103), 
(1006,29,103), 
(1007,26,102), 
(1008,22,103), 
(1009,30,103),
(1010,35,102), 
(1011,29,103), 
(1012,24,101),
(1013,22,103), 
(1014,38,101),
(1015,27,101);

Expected result using sample data:

101 | 'Marketing' | 2925
102 | 'Finance' | 3033.333333
103 | 'Operations' | 2450

Also, if you could suggest a better title for this question, that would be great :) Thanks!

Answer 1

You can try to use JOIN with COUNT and SUM aggregate function.

SELECT d.DeptID,d.DeptName,SUM(e.EmpAge * 100)/COUNT(*) average_salary
FROM  Departments d
LEFT JOIN Employees e
ON d.DeptID = e.DeptID
GROUP BY d.DeptID,d.DeptName

sqlfiddle