GROUP BY

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

Core Concepts

flowchart LR
  A[Raw rows] --> B[GROUP BY key]
  B --> C[Rows partitioned into groups]
  C --> D[Aggregate per group]

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