SUM

Hook: Interviewers like SUM because it looks simple, but it quietly tests whether you understand NULL, grouping, and the difference between a total and a row count.

Question: What does SUM do in SQL?

Answer: SUM is an aggregate function that adds numeric values from multiple rows and returns one total. It ignores NULL values, so missing numbers do not get counted. If there are no non-NULL values to add, the result is NULL, not 0.

Interview-Ready Answer: In SQL, I use SUM to add numeric values across rows and get one total back. It ignores NULLs, and I usually pair it with GROUP BY when I need subtotals, like sales per region. One detail I always remember is that if no matching non-NULL rows exist, SUM returns NULL, so I use COALESCE when I need a guaranteed zero.

What SUM really is

SUM is an aggregate function — a function that compresses many rows into one result. Think of it like a cashier adding every valid receipt into one running total. The input must be numeric or an expression that becomes numeric, such as price * quantity.

How it works under the hood

1. The database first finds the rows that survive WHERE filtering.
2. For each surviving row, it evaluates the expression inside SUM, such as amount or price * quantity. An expression is just a calculation made from columns and values.
3. If that expression is NULL, the row is skipped. SUM does not add NULL as if it were zero.
4. The engine keeps a running accumulator, which is a small internal state that stores the partial total.
5. When the rows are done, the engine returns the final total. If there were no non-NULL values at all, the result is NULL.

When you add GROUP BY, the same idea happens once per group. For example, the database may keep one running total for each region, each customer, or each day.

When and why to use it

• To calculate totals, like revenue, units sold, or minutes watched.
• To calculate subtotals, like sales per department or per month.
• To calculate conditional totals, usually with CASE WHEN, such as paid revenue only.
• To compare thresholds, such as checking whether a customer spent more than 1000.

SUM vs other common aggregates

AVG is especially useful to compare with SUM: average is essentially SUM divided by the count of non-NULL rows.

Performance and practical details

The time complexity is usually O(n) over the qualifying rows, because the database must inspect each row that matches the filter. For a single total, memory is usually O(1); for grouped totals, memory is closer to O(g), where g is the number of groups. If there are 100,000 groups, a hash aggregate can use tens of MB or more depending on the engine and row width, and it may spill to disk if memory is too small.

Indexes do not make SUM free. A selective filter can help the engine read fewer rows, but the database still has to add the matching values. On a warm cache, a simple sum over millions of rows may finish in a few hundred milliseconds; on a cold disk, it may take seconds.

Two important engine details vary by database version and dialect:

• Return type: some engines widen integer inputs to a larger type to reduce overflow risk, while others keep the same numeric family. Check your database docs.
• Overflow and precision: for money, use DECIMAL or NUMERIC, not floating-point types like REAL, because floats can accumulate tiny rounding errors.

One more useful variant is SUM(DISTINCT col), which removes duplicate values before adding them. That can be helpful, but it usually costs more because the engine must deduplicate first.

Memory hook: think of SUM as a cash drawer: it adds every valid bill, skips empty envelopes, and if nobody puts money in, the drawer stays blank unless you tape on a zero with COALESCE.

Real-World Story: In an e-commerce checkout service, SUM is used to total line items for each order, such as SUM(price * quantity) grouped by order_id. That total feeds tax calculation, payment authorization, and the final invoice sent to the customer. If the team forgets that SUM returns NULL for an empty result set, a cart with no remaining billable items can produce null in the API instead of 0.

What goes wrong in production is easy to miss at first: the checkout page starts showing a blank total or a frontend error like 'cannot read amount from null'. Logs show entries such as total_amount=null for some orders, and support tickets spike because users cannot complete payment. The bug is not that SUM is broken; it is that the app assumed it would always return a number, so one edge case turned into a failed checkout flow.

Follow-up & Tricky Questions:

• What does SUM do with NULL values? It ignores them. That is why SUM(amount) and SUM(COALESCE(amount, 0)) can behave differently when you care about missing data.
• What is the difference between SUM and COUNT? SUM adds numeric values, while COUNT counts rows or non-NULL values. They answer different questions, even though both are aggregates.
• How do you calculate a conditional sum? Use SUM(CASE WHEN condition THEN value ELSE 0 END). This is the standard pattern for totals like paid revenue, refunded revenue, or active-user minutes.
• What does SUM(DISTINCT col) do? It deduplicates values before adding them, so repeated equal values are counted once. It is not the same as summing distinct rows from a GROUP BY.
• Why would you wrap SUM in COALESCE? Because SUM can return NULL for an empty result set. COALESCE gives you a default like 0 when your app needs a real number.
• Can you use SUM in WHERE? No, because WHERE runs before aggregation. Use HAVING for grouped filters, or put the aggregate in a subquery.
• Does SUM fix rounding problems automatically? No. If you sum floating-point values, you can still get tiny precision errors. For money, store values as DECIMAL or NUMERIC.
• Is SUM over an empty set 0? No, it is NULL. This is one of the most common interview traps.
• Can SUM be used in WHERE? No again; it belongs in SELECT or HAVING. The trick is remembering the order of SQL operations.

Common Mistakes:

• Assuming empty means zero: correction — SUM returns NULL when nothing non-NULL is available, so use COALESCE if you need 0.
• Using SUM to count rows: correction — use COUNT(*) for rows and COUNT(col) for non-NULL values.
• Summing money with floats: correction — use fixed-point DECIMAL or NUMERIC to avoid rounding surprises.
• Putting SUM in WHERE: correction — aggregate filters belong in HAVING or a subquery because WHERE runs first.

Memory Hook: SUM is the cash drawer: it adds every valid bill, skips empty envelopes, and if nobody puts money in, the drawer shows blank unless you paste on a zero with COALESCE.

Cheat Sheet:

• SUM adds numeric values across rows.
• It ignores NULL inputs.
• Empty result set means NULL, not 0.
• Use GROUP BY for subtotals.
• Use COALESCE(SUM(...), 0) when the app needs a guaranteed number.
• Prefer DECIMAL for currency.

Practice Tasks:

• Write a query that returns total sales per region.
• Write a conditional sum for only positive amounts.
• Change the sample data so every amount in one region is NULL, then observe the raw SUM result.