SQL SUM: correct totals, NULL, HAVING, and duplicate rows

SUM adds values, but a correct result starts earlier: with deciding what one row represents. If a join makes one order appear twice, the aggregate will count it twice. If an amount is unknown, displaying zero will not make it known.

I’ll show a basic total, grouping, filtering, missing values, and a running total using small synthetic datasets. I executed the shared examples in PostgreSQL 17.11 and SQLite 3.53.4. A separate pg_typeof block applies only to PostgreSQL. These results do not automatically establish behavior in SQL Server, MySQL, or other engines.

Data and the first total

Run the following block once in an empty test session. It creates temporary tables that disappear when the session ends. Execute the subsequent queries in the same connection. Names and data are identical in the Polish and English articles.

sales contains three sales lines. quantity is a number of items, and unit_price is a price per item in arbitrary whole units. There are no discounts, taxes, refunds, or multiple currencies. The second table, events, contains independent events with an amount; one event has an unknown amount. ISO date strings serve only to order the example, not to perform date arithmetic.

-- example: fixture
CREATE TEMP TABLE sales (
    id INTEGER PRIMARY KEY,
    product TEXT,
    quantity INTEGER,
    unit_price INTEGER
);
INSERT INTO sales VALUES
    (1, 'Phone', 2, 500),
    (2, 'Laptop', 1, 1200),
    (3, 'Tablet', 3, 300);

CREATE TEMP TABLE events (
    id INTEGER PRIMARY KEY,
    segment TEXT,
    event_day TEXT,
    amount INTEGER
);
INSERT INTO events VALUES
    (1, 'A', '2026-01-01', 200),
    (2, 'A', '2026-01-01', NULL),
    (3, 'B', '2026-01-02', 500),
    (4, 'B', '2026-01-02', 200);

First I multiply quantity by unit price within each row, then add the results:

-- example: total
SELECT SUM(quantity * unit_price) AS total
FROM sales;

The manual check is 2 × 500 + 1 × 1200 + 3 × 300 = 3100. Checking a small sample by hand is useful even when the full query handles much more data. A result of 2900 does not follow from this table.

SUM(quantity * unit_price) and SUM(quantity) * SUM(unit_price) answer different questions. The first totals line values; the second multiplies two independent totals and produces 12000 here. I do not substitute one for the other as a query “simplification.”

In a real report, define the unit and the grain of the data. If a table stores order lines, an order total repeated on every line is not a safe column to sum. SQL cannot recognize the mistake from the field name. I need to check which quantity occurs once and which is repeated across records.

GROUP BY, WHERE, and HAVING

GROUP BY divides records into groups and produces a total for each product:

-- example: groups
SELECT product, SUM(quantity * unit_price) AS total
FROM sales
GROUP BY product
ORDER BY product;

Explicit ORDER BY determines output order. Without it, I do not assume that groups will always appear in the same sequence. Each product has one line in this small dataset, but the query also sums multiple lines for the same product.

To select products with totals greater than 1000, I filter the groups:

-- example: having
SELECT product, SUM(quantity * unit_price) AS total
FROM sales
GROUP BY product
HAVING SUM(quantity * unit_price) > 1000
ORDER BY product;

Only Laptop qualifies. Phone has exactly 1000, and the condition is > 1000, not >= 1000. A boundary error is easy to overlook when the result looks plausible.

I repeat the aggregate expression in HAVING. PostgreSQL does not allow the total alias created in the same SELECT list to be used there. Other engines may permit that shortcut; this does not make it portable SQL. The SELECT documentation describes alias visibility.

WHERE does a different job: it selects the rows that enter the aggregate.

-- example: where
SELECT SUM(quantity * unit_price) AS total
FROM sales
WHERE quantity >= 2;

Phone and Tablet remain because both lines contain at least two items. The result is 1000 + 900 = 1900. Moving a condition between WHERE and HAVING without considering its meaning can change the business question. Filtering individual lines and filtering group totals are not interchangeable operations.

NULL, empty inputs, and COALESCE

Now I use events:

-- example: nulls
SELECT COUNT(*) AS rows_count,
       COUNT(amount) AS known_count,
       SUM(amount) AS total
FROM events;

There are four events but only three known amounts. SUM ignores NULL, so 900 is the total of the known amounts. It does not mean the missing event had an amount of zero. That is why I show COUNT(*) and COUNT(amount) beside the sum. The COUNT article explains the distinction between those counts.

For one existing row with an unknown amount:

-- example: all-null
SELECT COUNT(*) AS rows_count,
       SUM(amount) AS total,
       SUM(COALESCE(amount, 0)) AS inner_zero,
       COALESCE(SUM(amount), 0) AS outer_zero
FROM events
WHERE id = 2;

For an empty filtered input:

-- example: empty
SELECT COUNT(*) AS rows_count,
       SUM(amount) AS total,
       SUM(COALESCE(amount, 0)) AS inner_zero,
       COALESCE(SUM(amount), 0) AS outer_zero
FROM events
WHERE id > 9;

This distinction matters. SUM(COALESCE(amount, 0)) replaces the missing amount in each existing row with zero. If there are no rows, there is nothing to replace, and the result remains NULL. COALESCE(SUM(amount), 0) operates on the aggregate result, so it also replaces an empty-input sum with zero.

In the result tables, NULL denotes a missing value, not a four-letter string. Your SQL client may display it as an empty cell. Establish a presentation convention so readers do not confuse an unknown value with a broken output table.

Which version should you choose? I settle the meaning first. If the report should show zero when there are no events, an outer COALESCE may be appropriate. If a missing value means incomplete data, I preserve that information along with a missing-value count. Replacing it with zero is an interpretation decision, not merely a cosmetic adjustment. The PostgreSQL aggregate documentation describes empty-input behavior.

Without GROUP BY, these queries return one aggregate row even for an empty input. Grouping empty input does not automatically create a group for every product. To display categories with no events, begin with a category list and deliberately join it to the events. That is a separate concern from replacing a missing sum with zero.

Conditional totals

When I need several totals in one row, I can put CASE inside the aggregate:

-- example: conditional
SELECT SUM(CASE WHEN segment = 'A' THEN amount ELSE 0 END) AS total_a,
       SUM(CASE WHEN segment = 'B' THEN amount ELSE 0 END) AS total_b,
       SUM(CASE WHEN segment = 'C' THEN amount ELSE 0 END) AS total_c
FROM events;

There are no segment C events, but the input contains other rows. Each of those contributes ELSE 0 to the third expression, producing zero. If the entire input were empty, this expression alone would not guarantee zero; the empty-input rule still applies.

Notice segment A as well. One amount is unknown, but the known part totals 200. CASE does not repair completeness. If the reader needs the full segment amount, show the number of missing values or mark the result as partial. A number appearing in a report should not create a false impression of completeness.

DISTINCT and duplicates after JOIN

SUM(DISTINCT amount) adds distinct values, not distinct events:

-- example: distinct
SELECT SUM(amount) AS total,
       SUM(DISTINCT amount) AS distinct_values_total
FROM events;

Two separate events have an amount of 200. The ordinary total includes both, while DISTINCT keeps that value only once: 200 + 500 = 700. This is correct function behavior, but it answers the wrong question if we want the total for all events.

A related problem appears after a one-to-many join. I add synthetic tags only for this query:

-- example: join
WITH tags(event_id, tag) AS (
    VALUES (1, 'x'), (1, 'y'), (3, 'x'), (4, 'x')
)
SELECT SUM(e.amount) AS inflated_total
FROM events AS e
JOIN tags AS t ON t.event_id = e.id;

The first event has two tags, so its 200 is counted twice. The result of 1100 is inflated. Adding DISTINCT amount would not fix it: that would also remove the other legitimate event worth 200.

If I only need events that have at least one tag, I use an existence condition:

-- example: exists
WITH tags(event_id, tag) AS (
    VALUES (1, 'x'), (1, 'y'), (3, 'x'), (4, 'x')
)
SELECT SUM(e.amount) AS total
FROM events AS e
WHERE EXISTS (SELECT 1 FROM tags AS t WHERE t.event_id = e.id);

EXISTS checks whether a match is present without multiplying events rows. The total returns to 900. If you need totals by tag, you must also decide how to allocate an event across multiple categories. Those category totals may deliberately be nonadditive. No universal DISTINCT expression can make that decision for you.

Running totals

A window function retains the rows and adds a result calculated over a defined window:

-- example: running
SELECT id, event_day, amount,
       SUM(amount) OVER (
           ORDER BY event_day, id
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM events
ORDER BY event_day, id;

The running values are 200, 200, 700, and 900. The missing amount in the second row adds no value, but the row remains. id resolves the order of events on the same day, while ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW defines the frame from the start through the current row.

I keep those details explicit. A default frame with only ORDER BY event_day also includes peer rows with the same ordering value. Both events on the second day can then show 900. That may be appropriate for an end-of-day total, but it does not mean a total after each successive event. The PostgreSQL window-function guide explains the difference.

Ordering inside OVER controls the calculation. The final ORDER BY controls presentation. I retain both because they serve different purposes. If you add a WHERE filter, the window sees only the selected rows; it will not remember earlier events removed from the query input.

Types, overflow, and precision

Our small values stay within the types’ ranges. For larger datasets, I check both the aggregate’s return type and the type of the expression passed to it. In PostgreSQL, multiplying two integer values can overflow before SUM receives the result. Casting one factor before multiplication widens that operation:

-- example: widen
SELECT SUM(CAST(quantity AS BIGINT) * unit_price) AS total
FROM sales;

This does not provide an unlimited range; BIGINT has limits too. Choose an appropriate exact type for your engine when needed. Casting the result of quantity * unit_price is too late if that multiplication has already failed.

The following block is PostgreSQL only:

-- example: postgres-types
SELECT pg_typeof(SUM(quantity)) AS integer_sum_type,
       pg_typeof(SUM(CAST(quantity AS BIGINT))) AS bigint_sum_type
FROM sales;

For integer input, the sum is bigint; for bigint, it is numeric. SQLite follows different rules, and an integer sum can raise an overflow error. A SQLite test does not establish PostgreSQL type behavior. The SQLite aggregate documentation describes its rules.

Floating-point results are approximations and can depend on calculation order. If you need exact decimal values, choose the type and scale deliberately. An integer example producing 3100 is not evidence of financial precision. Subsequent division of a sum also needs a type check: integer division can discard the fractional part. Verify the rule in your target engine.

Exercise and result checks

Predict the result before running the query:

-- example: exercise
SELECT segment, COUNT(*) AS rows_count,
       COUNT(amount) AS known_count, SUM(amount) AS total
FROM events
GROUP BY segment
HAVING SUM(amount) >= 200
ORDER BY segment;

Both segments pass >= 200, but A has only one known amount across two events. Changing the threshold to > 200 would leave only B. This small test covers a boundary condition, grouping, and data completeness together.

Before adding a total to a report, I answer four questions: what a row represents, which records entered the calculation, how many values are unknown, and whether a join multiplied the data. Only then do I assess the number itself. A small case calculated by hand provides a useful reference point for a larger query.