SQL Aggregates and GROUP BY

Summarize many rows into one row per group.

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

Explanation

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.

Explore the dry run

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

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.

SQL Aggregates and GROUP BY

Explanation

Follow the example

Cost and common mistakes

Try it yourself

Further reading