Average Salary by Department in SQL – Practical PostgreSQL Example

To solve this exercise we need:
· JOIN to combine emp with dept
· GROUP BY to group by department
· AVG() to calculate the average
· COUNT() to count employees

SELECT 
    d.name AS department, -- takes the column 'name' from table dept (alias d). AS department renames the output column (alias) displayed as 'department'.
    COUNT(e.emp_id) AS num_employees, -- counts employees (from table emp (alias e), column emp_id). The output alias will appear as 'num_employees'.
    ROUND(AVG(e.salary), 2) AS avg_salary -- calculates the arithmetic average of salaries within each group. The number 2 rounds to two decimal places.
FROM emp e -- main table providing data, assigned alias e.
JOIN dept d ON e.dept_id = d.dept_id -- joins tables emp and dept. The ON condition specifies that only rows with the same dept_id in both tables are combined.
GROUP BY d.name -- groups results by department name, so all employees sharing the same d.name are treated as one group for COUNT() and AVG() functions.
ORDER BY avg_salary DESC; -- sorts results by average salary in descending order.