SQL SUM Function: Totals, NULL and Duplicate Rows

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

SUM(amount) adds ten, thirty, twenty, twenty and five, returning eighty-five. Sale 104 has no known amount and therefore contributes no numeric value. This is not the same as proving that sale has a zero amount. Pair SUM with a count of known and unknown amounts if completeness matters, so a reader can distinguish a full total from a total of currently known values.

SUM returns NULL when no non-NULL input is available, rather than always returning zero. COALESCE(SUM(amount),0) can provide a display or reporting default if an empty selection should mean no revenue. Decide carefully whether the same replacement is appropriate for an all-unknown selection. Zero says a known numeric total; NULL says no total could be derived from known input. Moving a fallback inside or outside the aggregate can change the interpretation of missing values.

GROUP BY status produces a separate total for paid and pending sales. Paid amounts total fifty-five and known pending amounts total thirty. Conditional aggregation can compute those measures as separate columns in one summary by applying CASE inside SUM. Adding quantity times unit_price is another useful pattern when the source represents line items, but rounding and numeric precision should be defined before the expression is used for financial reporting.

SUM(DISTINCT amount) adds different amount values only, not distinct sale records. Here it returns sixty-five because the repeated twenty contributes once. That is not a valid total of sales: two real purchases can have the same price. When a join accidentally repeats transactions, fix the join or aggregate each relation at its intended grain. DISTINCT on the amount can undercount legitimate repeated values while appearing to correct duplicated rows. Use a decimal type with suitable precision for money; floating-point storage can introduce rounding surprises.

SUM is supported by all four systems, but numeric result types and overflow rules differ. PostgreSQL and MySQL preserve exact numeric arithmetic for suitable decimal inputs; SQLite’s ordinary DECIMAL declaration gives numeric affinity rather than fixed decimal storage. For strict currency calculations in SQLite, consider integer minor units with an explicit unit policy.

Check the engine-specific syntax and version requirements in SQLite aggregate SUM.

Calculate the total known amount for paid sales only. Keep the filter outside the aggregate because the entire input is restricted to paid records.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT SUM(amount) AS paid_total
FROM sales WHERE status='paid';

paid_total
55

The qualifying amounts are ten, twenty, twenty and five. Adding them gives fifty-five. The pending thirty and missing amount do not enter this input. The query includes the anonymous paid sale because the task concerns all paid sales rather than identified customers only.

Compare SUM(amount) with SUM(DISTINCT amount): eighty-five versus sixty-five exposes the legitimate repeated twenty. Filter to only sale 104 and observe NULL, then apply COALESCE to the final sum and explain the policy behind the resulting zero. Filter to pending status and pair SUM with COUNT(*) and COUNT(amount) to show why thirty is only a known-value subtotal.