More aggregate function examples: the employee database

The employee database

EMPLOYEES holds 24 rows, one per person, with a salary, a dept_id, and a job_id. Every aggregate example on this page is really the same question asked a different way: how does pay break down once you group people together?

The three tables behind every query on this page.

Table	Rows	Relevant columns
DEPARTMENT	6	dept_id, dept_name, location, budget
JOBS	6	job_id, job_title, min_salary, max_salary
EMPLOYEES	24	emp_id, first_name, last_name, email, salary, dept_id, job_id

More Section 4.9 examples

Aggregates, again, on payroll data

Same five functions, same GROUP BY rule, same WHERE-before-HAVING-after order. What is new here is how visibly the null-handling rule and the grouping rule change the answer.

COUNT(*) versus COUNT(column)

Three employees, Mei Chen, Grace Adeyemi, and Omar Farouk, have no email address on file.

SELECT COUNT(*), COUNT(email)
FROM   EMPLOYEES;
-- COUNT(*) is 24, every row counts; COUNT(email) is 21, the three NULL emails are skipped

SUM, AVG, MIN, and MAX

SELECT SUM(salary), AVG(salary), MIN(salary), MAX(salary)
FROM   EMPLOYEES;
-- total payroll $1,865,000; average $77,708.33; low $47,000 (Grace Adeyemi); high $145,000 (Carolyn Reyes, the president)

GROUP BY department

Grouping straight off EMPLOYEES only shows departments that actually have a row to group.

SELECT D.dept_name, COUNT(*) AS headcount, ROUND(AVG(E.salary), 2) AS avg_salary
FROM   EMPLOYEES E JOIN DEPARTMENT D ON E.dept_id = D.dept_id
GROUP BY D.dept_name
ORDER BY avg_salary DESC;

Result: 4 rows, one per department that has employees.

dept_name	headcount	avg_salary
Accounting	5	89,800.00
Engineering	9	85,111.11
Sales	7	65,571.43
Human Resources	3	63,666.67

GROUP BY job title

SELECT J.job_title, COUNT(*) AS headcount, AVG(E.salary) AS avg_salary
FROM   EMPLOYEES E JOIN JOBS J ON E.job_id = J.job_id
GROUP BY J.job_title
ORDER BY avg_salary DESC;

Result: 6 rows, one per job title.

job_title	headcount	avg_salary
President	1	145,000.00
Manager	4	91,000.00
Software Engineer	8	83,000.00
Accountant	3	72,000.00
Sales Rep	6	60,166.67
HR Specialist	2	57,500.00

HAVING: filtering the groups

SELECT D.dept_name, COUNT(*) AS headcount
FROM   EMPLOYEES E JOIN DEPARTMENT D ON E.dept_id = D.dept_id
GROUP BY D.dept_name
HAVING COUNT(*) > 5;
-- only Engineering qualifies, with 9 employees; every other department has 7 or fewer

WHERE and HAVING together

Which departments have more than one employee earning over $70,000?

SELECT D.dept_name, COUNT(*) AS high_earners
FROM   EMPLOYEES E JOIN DEPARTMENT D ON E.dept_id = D.dept_id
WHERE  E.salary > 70000
GROUP BY D.dept_name
HAVING COUNT(*) > 1
ORDER BY high_earners DESC;

Result: 3 rows. Human Resources drops out here.

dept_name	high_earners
Engineering	7
Accounting	4
Sales	2