SUM

SUM adds numeric values across rows.

It is commonly used for revenue, expenses, quantities, and scores.

Core Concepts

flowchart LR
  A[Numeric rows] --> B[SUM(column)]
  B --> C[Total value]
  C --> D[Optional GROUP BY for per-category totals]

Pattern	Example
Whole-table total	SELECT SUM(amount) FROM expenses;
Grouped totals	SELECT category, SUM(amount) ... GROUP BY category;

Code Examples

-- Setup expense data.
CREATE TABLE expenses_sum (
  expense_id INTEGER PRIMARY KEY,
  category   TEXT NOT NULL,
  amount     REAL NOT NULL
);

INSERT INTO expenses_sum (category, amount)
VALUES
  ('travel', 250.00),
  ('travel', 100.00),
  ('food', 40.00),
  ('food', 35.00);

Expected output
Table created and four expense rows inserted.

-- Total spend across all categories.
SELECT SUM(amount) AS total_spend
FROM expenses_sum;

total_spend
425.0

-- Total spend per category.
SELECT category, SUM(amount) AS category_total
FROM expenses_sum
GROUP BY category
ORDER BY category;

category	category_total
food	75.0
travel	350.0

SQLite-Specific Nuances

SQLite Nuance

If no input rows match, SUM() returns NULL (not 0) in SQLite, following SQL semantics.

Common Pitfalls / Best Practices

Pitfall

Assuming a missing total is always 0; it may be NULL when no rows match.

Best Practice

For reports that require explicit zero, use COALESCE(SUM(amount), 0).

Quick Challenge

Return total travel expense from expenses_sum.

View Solution

SELECT SUM(amount) AS travel_total
FROM expenses_sum
WHERE category = 'travel';

travel_total
350.0