GROUP BY

GROUP BY splits rows into groups so aggregates can be computed per group instead of over the whole table.

Core Concepts

Grouping target	Example
Single column	GROUP BY category
Multiple columns	GROUP BY category, month

Without GROUP BY

SELECT SUM(amount) AS total_amount
FROM payments_agg;

total_amount
430.0

With GROUP BY

SELECT category, SUM(amount) AS total_amount
FROM payments_agg
GROUP BY category
ORDER BY category;

category	total_amount
cloud	130.0
tools	300.0

Code Examples

-- Count and sum per category.
SELECT
  category,
  COUNT(*)    AS payment_count,
  SUM(amount) AS total_amount
FROM payments_agg
GROUP BY category
ORDER BY category;

category	payment_count	total_amount
cloud	2	130.0
tools	3	300.0

-- Group by two keys.
CREATE TABLE payments_by_month (
  category TEXT NOT NULL,
  month    TEXT NOT NULL,
  amount   REAL NOT NULL
);

INSERT INTO payments_by_month (category, month, amount)
VALUES
  ('tools', '2026-01', 120),
  ('tools', '2026-02', 180),
  ('cloud', '2026-01', 60),
  ('cloud', '2026-02', 70);

SELECT category, month, SUM(amount) AS total_amount
FROM payments_by_month
GROUP BY category, month
ORDER BY category, month;

category	month	total_amount
cloud	2026-01	60.0
cloud	2026-02	70.0
tools	2026-01	120.0
tools	2026-02	180.0

SQLite-Specific Nuances

SQLite Nuance

SQLite can be permissive with selected columns outside GROUP BY, but this can produce results that are hard to reason about and less portable.

Common Pitfalls / Best Practices

Pitfall

Selecting non-grouped, non-aggregated columns and assuming they represent a deterministic row.

Best Practice

For grouped queries, select only grouped columns plus aggregated expressions.

Quick Challenge

From payments_agg, return average amount per category.

View Solution

SELECT category, AVG(amount) AS avg_amount
FROM payments_agg
GROUP BY category
ORDER BY category;

category	avg_amount
cloud	65.0
tools	100.0