SQL: Aggregates, Grouping and Joins

Every SELECT you have written so far gives one output row for every row that matched. An aggregate function breaks that rule: it takes a whole column of values and returns a single value. Five are on your syllabus — MAX, MIN, AVG, SUM, COUNT.

Two tables run through this entire chapter. Build them once and every query in every section will work.

CREATE DATABASE company;
USE company;

CREATE TABLE dept (
  deptno INT PRIMARY KEY,
  dname  VARCHAR(20),
  city   VARCHAR(20)
);
INSERT INTO dept VALUES
(10, 'Sales',     'Mumbai'),
(20, 'Technical', 'Bengaluru'),
(30, 'HR',        'Delhi');

CREATE TABLE emp (
  empno  INT PRIMARY KEY,
  ename  VARCHAR(20),
  deptno INT,
  salary DECIMAL(10,2),
  bonus  DECIMAL(10,2),
  doj    DATE
);
INSERT INTO emp VALUES
(101, 'Ananya Iyer',  20,  85000, 12000, '2019-06-10'),
(102, 'Rohit Sharma', 10,  62000,  NULL, '2020-01-15'),
(103, 'Meera Nair',   20,  91000, 15000, '2018-03-01'),
(104, 'Vikram Singh', 30,  47000,  5000, '2021-07-20'),
(105, 'Priya Menon',  10,  62000,  8000, '2020-11-05'),
(106, 'Arjun Desai',  20, 120000,  NULL, '2017-02-11'),
(107, 'Kavya Reddy',  30,  54000,  6000, '2022-08-30'),
(108, 'Imran Khan', NULL,  40000,  4000, '2023-04-02');

This is what EMP actually holds. Two gaps are deliberate: Rohit and Arjun have no bonus recorded, and Imran has no department assigned. Almost every mark students lose in this unit comes from those two NULLs.

And DEPT:

Worked example — all five functions in one query.

SELECT MAX(salary) AS highest,
       MIN(salary) AS lowest,
       SUM(salary) AS total,
       AVG(salary) AS average,
       COUNT(*)    AS employees
FROM emp;

Real output:

Eight rows went in, one row came out. That is the whole idea. Notice AVG came back as 70125.000000 — MySQL widens the result of AVG, so do not be surprised by the trailing zeros.

The NULL rule — learn this sentence. COUNT(*) counts rows. Every other aggregate, including COUNT(column), looks only at NON-NULL values. Here is the proof on our own table.

SELECT COUNT(*)      AS rows_in_table,
       COUNT(bonus)  AS bonus_values,
       COUNT(deptno) AS deptno_values,
       COUNT(DISTINCT salary) AS distinct_salaries,
       COUNT(DISTINCT deptno) AS distinct_depts
FROM emp;

Read that row slowly. COUNT(*) says 8 because there are eight rows. COUNT(bonus) says 6 because two bonuses are NULL. COUNT(deptno) says 7 because one department is NULL. COUNT(DISTINCT salary) says 7 because 62000 appears twice (Rohit and Priya). Same table, four different numbers — and the examiner will hand you exactly one of them.

Why AVG is not what students expect. If NULLs are skipped, they are skipped in the divisor too. AVG is the sum divided by the non-null count, never by the row count.

SELECT SUM(bonus)   AS sum_bonus,
       COUNT(bonus) AS non_null_count,
       AVG(bonus)   AS avg_bonus,
       SUM(bonus)/COUNT(bonus) AS sum_over_nonnull,
       SUM(bonus)/COUNT(*)     AS sum_over_all_rows
FROM emp;

AVG(bonus) equals 8333.333333, which is 50000/6. It is not 6250, which is 50000/8. If a question asks for the average bonus per employee including those who got none, AVG is the wrong tool — you must write SUM(bonus)/COUNT(*) yourself.

An all-NULL set gives NULL, not zero. Rohit and Arjun are the only two employees here with no bonus:

SELECT SUM(bonus) AS s, AVG(bonus) AS a, COUNT(bonus) AS c, COUNT(*) AS r
FROM emp WHERE empno IN (102,106);

SUM and AVG return NULL, COUNT(bonus) returns 0, COUNT(*) returns 2. COUNT is the only aggregate that can never come back NULL.

NULLs also escape your WHERE clause. This surprises everyone:

4 + 2 = 6, not 8. The two NULL-bonus rows fail both conditions, because any comparison with NULL is UNKNOWN, not true and not false. To catch them you need WHERE bonus IS NULL.

MAX and MIN are not only for numbers. On text they mean alphabetical order; on dates, latest and earliest.

SELECT MAX(ename) AS last_name_alpha, MIN(doj) AS earliest_joining FROM emp;

The query you must never write. An aggregate returns one value, so it cannot sit beside an ordinary column that has eight values:

SELECT ename, MAX(salary) FROM emp;

MySQL 8 refuses it outright:

ERROR 1140 (42000): In aggregated query without GROUP BY, expression #1 of SELECT
list contains nonaggregated column 'company.emp.ename'; this is incompatible with
sql_mode=only_full_group_by

MySQL names the column in full, database.table.column, so company in that message is simply the database you are working in — you will see your own database's name there.

To get the name of the highest-paid employee you must ask twice. First find the maximum:

SELECT MAX(salary) AS highest FROM emp;

Then look up the row that carries that value:

SELECT ename, salary FROM emp WHERE salary = 120000;

Two ordinary queries, one after the other. That is all your syllabus asks for.

A bare aggregate gives you one number for the whole table. Usually you want one number per department, per city, per year. That is GROUP BY.

Think of it physically. GROUP BY deptno sorts the eight employee rows into piles — one pile for deptno 10, one for 20, one for 30, and one for the rows where deptno is NULL. Then the aggregate is computed separately inside each pile, and each pile contributes one row of output.

Worked example — department-wise headcount and wage bill.

SELECT deptno, COUNT(*) AS staff, SUM(salary) AS wagebill, AVG(salary) AS avg_sal
FROM emp
GROUP BY deptno;

Real output, exactly as MySQL printed it:

Two things to notice, and both are examinable.

One: the NULL group is real. Imran Khan has no department, and instead of disappearing he forms a fourth group whose label is NULL. This is the one place in SQL where NULLs are treated as equal to one another — NULL = NULL is never true in a WHERE clause, but GROUP BY still puts all NULL rows in the same pile. So the answer to "how many rows will this query return?" is 4, not 3.

Two: the output is not sorted. Look at the order — 20, 10, 30, NULL. GROUP BY groups; it does not promise to sort. If the question says "display department-wise" you may get away with it, but if it says "in ascending order of department" you must add ORDER BY:

SELECT deptno, COUNT(*) FROM emp GROUP BY deptno ORDER BY deptno;

NULL sorts first in ascending order in MySQL.

The NULL rule still applies inside every group. This is the query that catches people who thought they had understood section 1:

SELECT deptno, COUNT(*) AS rows_in_group, COUNT(bonus) AS bonus_paid,
       SUM(bonus) AS bonus_total
FROM emp
GROUP BY deptno;

Department 20 has three employees but only two recorded bonuses, so AVG(bonus) for that group is 27000/2 = 13500.00, not 27000/3 = 9000. Verified:

SELECT SUM(bonus) AS s, COUNT(*) AS rows_, COUNT(bonus) AS nn, AVG(bonus) AS a
FROM emp WHERE deptno = 20;

The SELECT-list rule. Anything in your SELECT list that is not wrapped in an aggregate must appear in the GROUP BY. It has to — the database can only print one value per group, and only the grouping column is guaranteed to have exactly one value inside a group. Break the rule and MySQL 8 stops you:

SELECT deptno, ename, COUNT(*) FROM emp GROUP BY deptno;

ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and
contains nonaggregated column 'company.emp.ename' which is not functionally
dependent on columns in GROUP BY clause; this is incompatible with
sql_mode=only_full_group_by

Note the error number carefully, because the same mistake produces two different ones. With no GROUP BY at all you get ERROR 1140 (section 1); with a GROUP BY present but the column missing from it you get ERROR 1055. Both say the same thing: department 20 holds three names and only one count, so there is no single name MySQL could print.

Grouping on two columns. Then a group is one distinct combination of the two values:

SELECT deptno, salary, COUNT(*) AS how_many
FROM emp
GROUP BY deptno, salary
ORDER BY deptno, salary;

Seven rows, because seven distinct (deptno, salary) pairs exist. Only Rohit and Priya share one — both are 62000 in department 10 — so that pair shows how_many = 2.

Grouping on an expression. You are not limited to bare columns. To count hires per year:

SELECT YEAR(doj) AS joining_year, COUNT(*) AS joined
FROM emp
GROUP BY YEAR(doj)
ORDER BY joining_year;

Seven output rows for eight employees — 2020 is the only year with two joinings (Rohit in January, Priya in November). Whatever you group on must be repeated in the GROUP BY clause exactly as it appears, brackets and all.

This is the single most-tested distinction in Unit 3, and it is decided entirely by when each clause runs. Memorise this order — the whole topic falls out of it:

FROM  ->  WHERE  ->  GROUP BY  ->  HAVING  ->  SELECT  ->  ORDER BY

WHERE runs before the groups exist, so it can only test one row at a time and can never contain an aggregate. HAVING runs after the groups have been formed and the aggregates computed, so its condition is about the group as a whole.

Worked example — departments with more than one employee.

SELECT deptno, COUNT(*) AS staff
FROM emp
GROUP BY deptno
HAVING COUNT(*) > 1;

Four groups were formed; the NULL group had only Imran in it, so COUNT(*) > 1 was false for it and that group was dropped. Notice the group vanished, not the row — that is what HAVING does.

HAVING on an average.

SELECT deptno, AVG(salary) AS avg_sal
FROM emp
GROUP BY deptno
HAVING AVG(salary) > 60000;

HR (deptno 30) averages 50500.00 and the NULL group averages 40000.00, so both fail the test.

The query that needs both clauses. Read this requirement carefully: "Considering only employees earning at least Rs 50,000, display departments that still have more than one such employee, along with their total salary." There are two filters here of two different kinds — "earning at least 50000" is about a row, "more than one such employee" is about a group. So the answer needs WHERE and HAVING.

SELECT deptno, COUNT(*) AS staff, SUM(salary) AS wagebill
FROM emp
WHERE salary >= 50000
GROUP BY deptno
HAVING COUNT(*) > 1
ORDER BY deptno;

Trace it. WHERE first removes Vikram (47000) and Imran (40000), leaving six rows. Those six group into 10 (two people), 20 (three people) and 30 (one person — only Kavya survives). HAVING then removes department 30 because its surviving count is 1. Here is the same query without the HAVING, so you can see department 30 sitting there before it gets removed:

SELECT deptno, COUNT(*) AS staff FROM emp
WHERE salary >= 50000 GROUP BY deptno ORDER BY deptno;

The NULL group is gone from both — not because of HAVING, but because WHERE had already deleted Imran's row. That is the ordering doing its work.

Putting an aggregate in WHERE is an error, not a style mistake.

SELECT deptno, COUNT(*) FROM emp WHERE COUNT(*) > 1 GROUP BY deptno;

ERROR 1111 (HY000): Invalid use of group function

It cannot work: at the moment WHERE runs, no group exists, so there is nothing to count.

HAVING can test an aggregate you never display. This is perfectly legal and the board likes it:

SELECT deptno FROM emp GROUP BY deptno HAVING MAX(salary) > 90000;

Only Technical has anyone above 90000 (Arjun, at 120000). MAX(salary) never appears in the output, but HAVING could still use it.

HAVING without GROUP BY. Rare, but valid — the entire table is treated as one single group:

SELECT COUNT(*) AS n FROM emp HAVING COUNT(*) > 5;

Change the test to HAVING COUNT(*) > 50 and the query returns an empty set — the one group failed, so nothing is left. Do not confuse this with returning 0; there is no row at all.

Summary you can reproduce in the exam:

Write two table names in the FROM clause with no condition and SQL does the only thing it can: it pairs every row of the first table with every row of the second. That is the cartesian product, also called a cross join. It is the raw material every other join is carved out of.

To see the whole thing on one page, build a small four-employee table:

CREATE TABLE emp4 (
  empno  INT PRIMARY KEY,
  ename  VARCHAR(20),
  deptno INT
);
INSERT INTO emp4 VALUES
(101, 'Ananya Iyer',  20),
(102, 'Rohit Sharma', 10),
(103, 'Meera Nair',   20),
(104, 'Vikram Singh', 30);

EMP4 — 4 rows, 3 columns:

DEPT — 3 rows, 3 columns:

4 rows times 3 rows should be 12 rows, and 3 columns plus 3 columns should be 6 columns. Count the rows first, with three ordinary queries:

SELECT COUNT(*) AS emp4_rows    FROM emp4;
SELECT COUNT(*) AS dept_rows    FROM dept;
SELECT COUNT(*) AS product_rows FROM emp4, dept;

Worked example — the whole product, printed in full.

SELECT * FROM emp4, dept;

Twelve rows. Six columns — and deptno appears twice, once from each table, because a cartesian product removes nothing. Also note the department rows came back in the order 30, 20, 10; row order in a product is not guaranteed and you should not depend on it.

CROSS JOIN is the same operation written more explicitly, and gives the identical 12 rows:

SELECT COUNT(*) AS rows_from_cross_join FROM emp4 CROSS JOIN dept;

Now look at the product as data, and the join condition writes itself. Go down those twelve rows and compare the two deptno columns side by side. In only four of them are the two values equal — Ananya with Technical, Rohit with Sales, Meera with Technical, Vikram with HR. The other eight rows are nonsense like "Ananya Iyer, HR". Write exactly that comparison as a WHERE condition and the nonsense disappears:

SELECT * FROM emp4, dept WHERE emp4.deptno = dept.deptno;

Twelve rows in, four rows out, still six columns. Exactly one surviving row per employee, and that is not luck — deptno is the primary key of DEPT, so each employee can match at most one department. An equi-join is a cartesian product with the wrong rows filtered out. That sentence is worth writing on your revision card.

Why forgetting the condition is a disaster. On the full tables:

SELECT COUNT(*) AS emp_times_dept FROM emp, dept;

8 x 3 = 24 rows, of which only 7 are meaningful. On a real payroll table of 5,000 employees and 40 departments the accidental product is 200,000 rows. Leaving out the join condition is the classic silent bug.

Ambiguous columns. Once both tables contribute a column called deptno, you may no longer say deptno on its own:

SELECT deptno FROM emp4, dept;

ERROR 1052 (23000): Column 'deptno' in field list is ambiguous

Qualify it — emp4.deptno or dept.deptno — or use table aliases, which is what you should do in the exam because it is shorter: SELECT E.ename, D.dname FROM emp E, dept D;. Once you declare an alias, you must use the alias everywhere; writing emp.ename after aliasing emp as E gives ERROR 1054: Unknown column 'emp.ename' in 'field list'.

Both joins answer the same question — which department does each employee belong to? — and on our tables both return the same seven rows. They differ in how the condition is written and in what comes back.

Equi-join. You state the matching condition yourself, using the equality operator. There are two accepted forms and the board accepts either.

SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno;

Count the columns: 9. EMP has 6, DEPT has 3, and deptno is printed twice — the join condition proved the two values are equal but did nothing about the duplication. The second form, SELECT * FROM emp JOIN dept ON emp.deptno = dept.deptno;, gives the identical seven rows and the identical nine columns.

Natural join. Write no condition at all. SQL finds every column name common to both tables — here just deptno — joins on equality of those columns, and prints each shared column once.

SELECT * FROM emp NATURAL JOIN dept;

Same seven rows, but 8 columns instead of 9, and deptno has moved to the front. That is the standard behaviour: the shared columns are printed first, once each, then the rest of the left table, then the rest of the right table. What natural join removes is the duplicate copy of the common column.

Both joins silently drop unmatched rows. EMP has eight employees but the join returned seven:

Imran Khan is missing. His deptno is NULL, and NULL is not equal to 10, 20 or 30 — it is not equal to anything, not even to another NULL. So no pairing in the cartesian product survives the condition and he disappears without any warning. If a report built on a join shows fewer people than the staff list, unmatched or NULL keys are the first thing to check.

The natural join trap the board loves. Natural join matches on every column with a shared name, not just the key you had in mind. Suppose the employee table also stored a posting city:

CREATE TABLE empc (empno INT, ename VARCHAR(20), deptno INT, city VARCHAR(20));
INSERT INTO empc VALUES
 (201,'Nikhil Rao',20,'Bengaluru'),
 (202,'Sneha Joshi',20,'Pune'),
 (203,'Tarun Bose',10,'Mumbai');

EMPC and DEPT now share two column names, deptno and city, so the natural join quietly requires both to match:

SELECT * FROM empc NATURAL JOIN dept;

Sneha Joshi has vanished. She is in department 20, but she is posted in Pune while Technical's office city is Bengaluru, so the hidden second condition rejected her. The equi-join, which only tests what you asked it to test, keeps all three:

SELECT * FROM empc E JOIN dept D ON E.deptno = D.deptno;

And when no column name matches at all, natural join gives up quietly. Suppose a staff table names its columns differently:

CREATE TABLE staff (eno INT, sname VARCHAR(20), dno INT);
INSERT INTO staff VALUES (301,'Nisha Rao',10),(302,'Farhan Ali',20);

staff and dept have no column name in common, so there is nothing for NATURAL JOIN to match on. It does not raise an error — it falls straight back to a full cartesian product:

2 x 3 = 6 both times — the natural join produced the very product it was supposed to filter. The equi-join has no such problem, because you name the columns yourself:

SELECT * FROM staff S, dept D WHERE S.dno = D.deptno;

That is why professionals write equi-joins even though natural joins are shorter.

Worked example — a join doing real work. "List employees earning above Rs 60,000 with their department and city, highest paid first."

SELECT E.ename, E.salary, D.dname, D.city
FROM emp E, dept D
WHERE E.deptno = D.deptno AND E.salary > 60000
ORDER BY E.salary DESC;

The join condition and the row filter share one WHERE, connected by AND. Keep the join condition first — it makes the query easier to read and to mark.

Everything in this chapter, in one query. Join, then group, then filter the groups:

SELECT D.dname, D.city, COUNT(*) AS staff, AVG(E.salary) AS avg_sal
FROM emp E JOIN dept D ON E.deptno = D.deptno
GROUP BY D.dname, D.city
HAVING COUNT(*) > 1
ORDER BY avg_sal DESC;

Imran is absent because the join dropped him, so the three group counts add to 7 and not 8 — a real difference between a payroll report and a headcount report, produced by nothing more than a NULL foreign key.