The Numbers Game
Concept:
Using aggregate functions (COUNT, SUM, AVG, MAX, MIN) and GROUP BY to analyze data.
Detective Indecks:
I don't need individual names anymore. I need patterns. How many people in each department? What's the average salary? Where's the money going?
Database:
You need aggregate functions, detective. COUNT, SUM, AVG, MAX, MIN—they crunch the numbers and give you the big picture.
Detective Indecks:
Show me how many employees are in each department.
Database:
`SELECT department_id, COUNT(*) FROM employees GROUP BY department_id`. GROUP BY splits the data into groups, and COUNT tells you how many rows are in each group.
Detective Indecks:
What about average salaries? I need to know if one department is getting paid suspiciously well.
Database:
`SELECT department_id, AVG(salary) FROM employees GROUP BY department_id`. AVG calculates the mean. You'll see which departments are overpaid—or underpaid.
Detective Indecks:
Can I see the highest salary in each department?
Database:
`SELECT department_id, MAX(salary) FROM employees GROUP BY department_id`. MAX finds the top value in each group. Perfect for spotting outliers.
Detective Indecks:
And total payroll per department?
Database:
`SELECT department_id, SUM(salary) FROM employees GROUP BY department_id`. SUM adds up all salaries in each group. Follow the money, detective.
Detective Indecks:
Beautiful. Time to see where the numbers don't add up.
Example Code:
SELECT department_id, COUNT(*) as employee_count FROM employees GROUP BY department_id;Your Assignment
Calculate the total number of employees in each department. Use COUNT and GROUP BY to see how staff are distributed across the company.
Sql Console
sql>