SQLite GROUP BY: Group Rows and Aggregate with COUNT, SUM and AVG (Tested)

Ninad Pathak

October 7, 2026

SUM(units) on its own returns one grand total for the whole table. A total per product or per month needs GROUP BY. In this article I’ll walk you through it with real output, from a simple per-product count to grouping by month.

TLDR

GROUP BY product turns every set of rows with the same product into one output row.
Pair it with aggregate functions such as COUNT(*) and SUM().

WHERE filters rows before grouping. HAVING filters groups after.
Group by several columns, or by an expression such as strftime('%Y-%m', sold_on) for monthly totals.
NULLs form their own group.

What is GROUP BY in SQLite?

GROUP BY splits the result of a SELECT into groups of rows that share the same values, and returns one row per group. Aggregate functions then work on each group separately. Without GROUP BY, an aggregate covers the whole table:

sqlite> SELECT SUM(units) AS total_units FROM sales;
total_units
-----------
23

SQLite GROUP BY syntax

SELECT column, aggregate_function(column)
FROM table_name
WHERE row_condition
GROUP BY column
HAVING group_condition
ORDER BY column;

Logically, the clauses run in this order:

Step	Clause	What it does
1	`FROM`	Picks the table
2	`WHERE`	Filters rows
3	`GROUP BY`	Forms the groups
4	`HAVING`	Filters groups
5	`SELECT`	Computes the output columns and aggregates
6	`ORDER BY`	Sorts the result

That’s why WHERE can’t use an aggregate and HAVING can.

How to group rows in SQLite

To show you how grouping works, I will use a small sales table with six rows:

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  sold_on TEXT NOT NULL,
  product TEXT NOT NULL,
  region TEXT,
  units INTEGER NOT NULL,
  unit_price REAL NOT NULL
);
INSERT INTO sales (sold_on, product, region, units, unit_price) VALUES
  ('2026-07-03', 'T-shirt', 'North', 5, 10.0),
  ('2026-07-19', 'Hoodie',  'South', 3, 15.0),
  ('2026-08-02', 'T-shirt', 'South', 2, 10.0),
  ('2026-08-14', 'Hoodie',  'North', 4, 15.0),
  ('2026-08-30', 'T-shirt', 'North', 3, 12.0),
  ('2026-09-05', 'Cap',     NULL,    6, 8.0);

sqlite> SELECT * FROM sales;
id  sold_on     product  region  units  unit_price
--  ----------  -------  ------  -----  ----------
1   2026-07-03  T-shirt  North   5      10.0
2   2026-07-19  Hoodie   South   3      15.0
3   2026-08-02  T-shirt  South   2      10.0
4   2026-08-14  Hoodie   North   4      15.0
5   2026-08-30  T-shirt  North   3      12.0
6   2026-09-05  Cap              6      8.0

Step 1: Pick the column to group by

Grouping by product gives one row per product. SUM(units) adds up the units in each group:

sqlite> SELECT product, SUM(units) AS units FROM sales GROUP BY product;
product  units
-------  -----
Cap      6
Hoodie   7
T-shirt  10

Step 2: Add the aggregates you need

A single query can return several aggregates for each group:

sqlite> SELECT product, COUNT(*) AS sales, SUM(units) AS units, SUM(units * unit_price) AS revenue, AVG(unit_price) AS avg_price FROM sales GROUP BY product;
product  sales  units  revenue  avg_price
-------  -----  -----  -------  ----------------
Cap      1      6      48.0     8.0
Hoodie   2      7      105.0    15.0
T-shirt  3      10     106.0    10.6666666666667

Step 3: Sort the groups

ORDER BY runs after grouping, so it can sort by an aggregate’s alias:

sqlite> SELECT product, SUM(units) AS units FROM sales GROUP BY product ORDER BY units DESC;
product  units
-------  -----
T-shirt  10
Hoodie   7
Cap      6

Examples

These examples run against the same six rows.

Count rows per group

sqlite> SELECT product, COUNT(*) AS sales FROM sales GROUP BY product;
product  sales
-------  -----
Cap      1
Hoodie   2
T-shirt  3

Group by multiple columns

Each distinct pair of product and unit_price becomes its own group, so T-shirts at 10.00 and 12.00 are counted separately:

sqlite> SELECT product, unit_price, SUM(units) AS units FROM sales GROUP BY product, unit_price;
product  unit_price  units
-------  ----------  -----
Cap      8.0         6
Hoodie   15.0        7
T-shirt  10.0        7
T-shirt  12.0        3

Filter rows before grouping with WHERE

Only North sales go into the groups:

sqlite> SELECT product, SUM(units) AS units FROM sales WHERE region = 'North' GROUP BY product;
product  units
-------  -----
Hoodie   4
T-shirt  8

Filter groups with HAVING

HAVING drops whole groups. Here it keeps products that sold more than six units. See SQLite HAVING for more:

sqlite> SELECT product, SUM(units) AS units FROM sales GROUP BY product HAVING SUM(units) > 6;
product  units
-------  -----
Hoodie   7
T-shirt  10

Group by month

Group by an expression. strftime('%Y-%m', …) turns each date into its month. See SQLite date and time:

sqlite> SELECT strftime('%Y-%m', sold_on) AS month, SUM(units * unit_price) AS revenue FROM sales GROUP BY month ORDER BY month;
month    revenue
-------  -------
2026-07  95.0
2026-08  116.0
2026-09  48.0

List the values in each group

group_concat() joins the values of a group into one string:

sqlite> SELECT product, group_concat(DISTINCT region) AS regions FROM sales GROUP BY product;
product  regions
-------  -----------
Cap
Hoodie   South,North
T-shirt  North,South

The order inside each string is arbitrary unless you add ORDER BY inside the function call (SQLite aggregate functions).

Median per group

median() is available “since SQLite version 3.51.0 (2025-11-04) if the amalgamation is compiled using -DSQLITE_ENABLE_PERCENTILE” (SQLite aggregate functions). The macOS build used here has it:

sqlite> SELECT product, median(units) AS median_units FROM sales GROUP BY product;
product  median_units
-------  ------------
Cap      6.0
Hoodie   3.5
T-shirt  3.0

Edge cases

NULL is its own group. The Cap sale has no region, so it lands in its own NULL group (the shell prints NULL as a blank):

sqlite> SELECT region, COUNT(*) AS sales FROM sales GROUP BY region;
region  sales
------  -----
        1
North   3
South   2

COUNT(*) and COUNT(column) differ. COUNT(column) skips NULLs:

sqlite> SELECT COUNT(*) AS all_rows, COUNT(region) AS with_region FROM sales;
all_rows  with_region
--------  -----------
6         5

Columns that aren’t grouped or aggregated. SQLite allows them, unlike PostgreSQL. With one MAX() or MIN() in the query, SQLite takes those “bare” columns from the row that holds the maximum or minimum (SQLite SELECT). So sold_on below is the date of each product’s biggest sale:

sqlite> SELECT product, sold_on, MAX(units) AS best_units FROM sales GROUP BY product;
product  sold_on     best_units
-------  ----------  ----------
Cap      2026-09-05  6
Hoodie   2026-08-14  4
T-shirt  2026-07-03  5

Without MIN() or MAX(), the value comes from an arbitrary row in the group. Don’t rely on it.

Conclusion

Use GROUP BY whenever you want one result row per category, and pick the aggregates that answer your question. Put row filters in WHERE and group filters in HAVING. Then sort the result with ORDER BY.