Aggregate Functions

Aggregate functions turn many row values into one summary value.

They are essential for reporting, dashboards, and quick data health checks.

Core Concepts

flowchart LR
  A[Input rows] --> B[Aggregate function]
  B --> C[Single summary value]
  C --> D[Optional grouping into many summaries]

Function	Purpose	Example
COUNT()	Count rows/values	COUNT(*)
SUM()	Total numeric values	SUM(amount)
AVG()	Mean numeric value	AVG(score)
MIN()	Smallest value	MIN(price)
MAX()	Largest value	MAX(price)

Whole Table Summary

-- One result row for the whole input set.
SELECT
  COUNT(*)      AS row_count,
  SUM(amount)   AS total_amount,
  AVG(amount)   AS avg_amount
FROM payments_agg;

row_count	total_amount	avg_amount
e.g. 5	e.g. 430.00	e.g. 86.0

Grouped Summary

-- One result row per category.
SELECT
  category,
  COUNT(*)    AS entries,
  SUM(amount) AS total_amount
FROM payments_agg
GROUP BY category
ORDER BY category;

category	entries	total_amount
cloud	e.g. 2	e.g. 130.00
tools	e.g. 3	e.g. 300.00

Code Examples

-- Setup sample data for this module.
CREATE TABLE payments_agg (
  payment_id INTEGER PRIMARY KEY,
  category   TEXT NOT NULL,
  amount     REAL NOT NULL
);

INSERT INTO payments_agg (category, amount)
VALUES
  ('tools', 120.00),
  ('tools', 80.00),
  ('cloud', 60.00),
  ('cloud', 70.00),
  ('tools', 100.00);

Expected output
Table created and five rows inserted for aggregation practice.

-- Quick multi-metric summary.
SELECT
  COUNT(*)    AS row_count,
  MIN(amount) AS smallest,
  MAX(amount) AS largest
FROM payments_agg;

row_count	smallest	largest
5	60.0	120.0

SQLite-Specific Nuances

SQLite Nuance

SQLite aggregate behavior follows standard SQL, but numeric conversion can be influenced by SQLite's dynamic typing rules.

Use clear numeric columns for predictable totals and averages.

Common Pitfalls / Best Practices

Pitfall

Mixing grouped and non-grouped columns incorrectly, leading to confusing or non-portable results.

Best Practice

When using GROUP BY, aggregate every column that is not part of the grouping key.

Quick Challenge

Return a single row with total number of payments and total amount from payments_agg.

View Solution

SELECT
  COUNT(*)    AS payment_count,
  SUM(amount) AS total_amount
FROM payments_agg;

payment_count	total_amount
5	430.0