Counting and totals: GROUP BY and HAVING

Introduction

An aggregate function turns many rows into one value:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept TEXT,
  marks INTEGER,
  email TEXT
);
INSERT INTO students (name, dept, marks, email) VALUES
  ('Rahim', 'CSE', 85, 'rahim@mail.com'),
  ('Karim', 'EEE', 72, NULL),
  ('Nadia', 'CSE', 91, 'nadia@mail.com'),
  ('Tania', 'BBA', 64, NULL),
  ('Sumon', 'CSE', 58, 'sumon@mail.com'),
  ('Rina', 'EEE', 79, 'rina@mail.com');

SELECT COUNT(*) AS students, COUNT(email) AS with_email, COUNT(DISTINCT dept) AS depts
FROM students;

SELECT SUM(marks) AS total, ROUND(AVG(marks), 2) AS average, MIN(marks) AS lowest, MAX(marks) AS highest
FROM students;

ROUND(AVG(marks), 2) keeps two decimal places. In SQLite, dividing two whole numbers gives a whole number (7 / 2 is 3), but AVG always gives a decimal.

Reports rarely list every row: they count, add up and average. Aggregates and GROUP BY turn thousands of rows into a short summary per department, month or customer.

How it works

GROUP BY itself is worked through in the Example section below. First, the rule that tells WHERE and HAVING apart, which the example also uses.

WHERE or HAVING?

• WHERE filters rows, before they are grouped.
• HAVING filters groups, after the aggregates are worked out.

So WHERE marks >= 60 drops the failing students before counting, and HAVING COUNT(*) >= 2 drops the small departments after counting. An aggregate in WHERE is an error, because when WHERE runs there are no groups yet:

CREATE TABLE students (
  name TEXT,
  dept TEXT
);
INSERT INTO students VALUES ('Rahim', 'CSE'), ('Karim', 'EEE'), ('Nadia', 'CSE');

-- An aggregate in WHERE: an error. It belongs in HAVING.
SELECT dept FROM students WHERE COUNT(*) >= 2 GROUP BY dept;

The full order is now:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Syntax

SELECT dept, COUNT(*) AS students, ROUND(AVG(marks), 2) AS average
FROM students
WHERE marks >= 60
GROUP BY dept
HAVING COUNT(*) >= 2
ORDER BY dept;

Example

GROUP BY dept puts the rows with the same dept together, then the aggregates run once per group:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT,
  dept TEXT,
  marks INTEGER
);
INSERT INTO students (name, dept, marks) VALUES
  ('Rahim', 'CSE', 85), ('Karim', 'EEE', 72), ('Nadia', 'CSE', 91),
  ('Tania', 'BBA', 64), ('Sumon', 'CSE', 58), ('Rina', 'EEE', 79);

SELECT dept, COUNT(*) AS students, ROUND(AVG(marks), 2) AS average
FROM students
GROUP BY dept
ORDER BY dept;

-- Only passing students are counted; only departments with 2 or more of them are kept
SELECT dept, COUNT(*) AS passed
FROM students
WHERE marks >= 60
GROUP BY dept
HAVING COUNT(*) >= 2
ORDER BY passed DESC, dept;

The rule for SELECT with GROUP BY: every column must either be in the GROUP BY or be inside an aggregate. SELECT dept, name ... GROUP BY dept makes no sense (which of the group's names?). MySQL, PostgreSQL and Oracle refuse it; SQLite quietly picks one row's name, so follow the rule yourself.

Line by line

1. The first query groups the six students by dept, giving three groups: BBA, CSE and EEE.
2. COUNT(*) and ROUND(AVG(marks), 2) are worked out once per group, so each department gets one row.
3. In the second query, WHERE marks >= 60 drops Sumon before any grouping happens.
4. GROUP BY dept then counts the remaining students in each department.
5. HAVING COUNT(*) >= 2 keeps only departments with at least two passing students, which leaves CSE and EEE.
6. ORDER BY passed DESC, dept sorts the groups by their count, and then by name.

Expected result

dept  students  average
----  --------  -------
BBA   1         64.0
CSE   3         78.0
EEE   2         75.5
(3 rows)

dept  passed
----  ------
CSE   2
EEE   2
(2 rows)

In the real world

Sales dashboards, attendance summaries and exam statistics are GROUP BY queries. Data analysts write them all day, and spreadsheet pivot tables do the same job.

Common mistakes

• Putting an aggregate in WHERE, which is an error: it belongs in HAVING.
• Selecting a column that is neither grouped nor aggregated.
• Using COUNT(column) when you mean every row: it skips NULLs.

Best practices

• Name every aggregate column with AS.
• Filter rows in WHERE whenever possible, so there is less to group.
• Group by the id as well as the name, because two people can share a name.

Summary

Aggregate functions summarise rows, and GROUP BY applies them once per group. WHERE filters rows before grouping and HAVING filters groups afterwards, and every selected column must be grouped or aggregated.

Mark lesson complete