SQL Aggregates and GROUP BY

Before you start

SQL SELECT and the idea of a table.

Summarize many rows into one row per group.
Predict the sample output and explain the state changes.
Complete the practice task and check the boundary cases.

The concept

An aggregate function such as SUM, COUNT, AVG, MIN, or MAX combines many rows into one value. GROUP BY splits the rows into groups first, so each group produces one result row.

In the example, the three sales rows form two groups, pen and book. COUNT(*) counts the rows in each group and SUM(qty) adds up their quantities. Every selected column must be either a grouped column or an aggregate. Results are written below as column values separated by |, the default style of the SQLite command-line shell.

Code example

SQL

CREATE TABLE sales (item TEXT, qty INTEGER, price REAL);
INSERT INTO sales VALUES ('pen', 10, 1.5), ('pen', 5, 1.5), ('book', 2, 12.0);

SELECT item, SUM(qty) AS total_qty, COUNT(*) AS orders
FROM sales
GROUP BY item
ORDER BY item;

Output

book|2|1
pen|15|2

Example details

Follow the code

Open the walkthrough to inspect the example one step at a time.

Show guided walkthrough

Guided walkthroughReady

1CREATE TABLE sales (item TEXT, qty INTEGER, price REAL);

2INSERT INTO sales VALUES ('pen', 10, 1.5), ('pen', 5, 1.5), ('book', 2, 12.0);

4SELECT item, SUM(qty) AS total_qty, COUNT(*) AS orders

5FROM sales

6GROUP BY item

7ORDER BY item;

Follow the example

Start to inspect the source, values, and output. These prepared steps explain the published sample; grouped loop steps describe multiple iterations.

View the complete dry-run table

Prepared trace of the published program. Some rows group repeated iterations.
What happens	State after step	Output so far
Create an empty table with three columns.	`—`	—
Add three rows: two for pen and one for book.	`3 rows`	—
Put the rows into two groups: pen (2 rows) and book (1 row).	`3 rows`	—
Sort the group rows alphabetically, so book comes first.	`3 rows`	book\|2\|1 pen\|15\|2

Behind the scenes

How SQL processes this code

The database parses and plans the query, accesses the table, and returns the selected rows. Physical execution order may differ from the query’s written order.

Cost and common mistakes

A typical engine scans the table once and groups by hashing or sorting, about O(n) to O(n log n).

WHERE filters rows before grouping; use HAVING to filter the groups afterwards.
COUNT(column) skips NULL values, while COUNT(*) counts every row.

Try it yourself

Show only the items whose total quantity is more than 5.

Reveal answer and reasoning

Add HAVING SUM(qty) > 5 after GROUP BY item. Only pen|15|2 remains.