SUM() in SQL
Introduction
SUM() is an aggregate function that adds numeric values and returns their total. It answers business questions such as total revenue, units sold, payroll cost, outstanding balance, execution time, network usage, and project effort. SUM can operate across an entire filtered result, produce one total per group, or calculate running and partitioned totals as a window function.
SELECT SUM(salary) AS total_salary
FROM employees;
If employee salaries are 50,000, 60,000, and 70,000, the result is 180,000. Correct totals depend on more than addition: NULL behavior, numeric type, row grain, join multiplication, filters, rounding policy, and transaction visibility all affect the meaning of the result.
Basic Syntax
SUM(numeric_expression)
The expression is evaluated for every qualifying row, and its non-NULL results are added. Common inputs include salary, price, quantity, amount, revenue, tax, discount, balance, duration, bytes, and calculated expressions such as quantity multiplied by unit price.
A Basic SUM Example
| employee_id | salary |
|---|---|
| 1 | 50000 |
| 2 | 60000 |
| 3 | 70000 |
SELECT SUM(salary) AS total_salary
FROM employees;
The function adds 50,000 + 60,000 + 70,000 and returns one aggregate row labeled total_salary. A clear alias makes the result usable in application code and reports.
SUM() Ignores NULL Inputs
If the salary values are 50,000, 60,000, NULL, and 70,000, SUM returns 180,000. The NULL input does not contribute zero; it is excluded from the addition. This distinction matters because NULL normally means unknown or absent rather than a known numeric zero.
All NULL Values and Empty Results
If every evaluated value is NULL, or if no rows qualify, SUM generally returns NULL. That differs from COUNT(*), which returns zero for an empty result.
SELECT SUM(bonus) AS total_bonus
FROM employees
WHERE department = 'Unknown';
A NULL aggregate communicates that no numeric total could be formed. Whether an application should show NULL, zero, blank, or "not available" is a business and presentation decision.
Using COALESCE for a Zero Result
SELECT COALESCE(SUM(bonus), 0) AS total_bonus
FROM employees;
COALESCE() replaces a NULL aggregate with zero. This is common in dashboards and financial summaries where the empty total should display as zero. Do not apply it automatically when NULL carries a meaningful distinction, such as data not loaded or measurement unavailable.
Filtering Rows Before SUM()
SELECT SUM(total_amount) AS completed_sales
FROM orders
WHERE status = 'Completed';
WHERE removes noncompleted orders before SUM runs. Additional conditions can restrict by amount, customer, date, region, tenant, or any other row-level rule.
SELECT SUM(total_amount) AS qualifying_sales
FROM orders
WHERE status = 'Completed'
AND total_amount >= 100;
SUM() with GROUP BY
Without GROUP BY, SUM returns one total for the whole filtered result. GROUP BY divides rows into categories and returns one total per category.
SELECT department,
SUM(salary) AS total_salary
FROM employees
GROUP BY department;
If IT salaries are 50,000 and 70,000, the IT total is 120,000. If HR salaries are 60,000 and 65,000, the HR total is 125,000. A NULL department forms its own group unless filtered out.
Business Totals by Group
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
GROUP BY customer_id;
The same pattern totals units by product, sales by category, payroll by department, expenses by cost center, or transfer bytes by API endpoint. The selected grouping columns define the result grain.
Filtering Aggregate Groups with HAVING
SELECT customer_id,
SUM(total_amount) AS total_spent
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id
HAVING SUM(total_amount) > 5000;
WHERE limits source rows to completed orders. GROUP BY forms one set per customer. HAVING retains customers whose completed total exceeds 5,000. Aggregate conditions belong in HAVING because totals do not exist during the WHERE stage.
SUM(DISTINCT expression)
SELECT SUM(DISTINCT amount) AS distinct_amount_total
FROM payments;
If amounts are 100, 100, 200, and 300, ordinary SUM returns 700 while SUM(DISTINCT amount) returns 600. DISTINCT removes duplicate numeric values, not duplicate business records.
Why SUM(DISTINCT) Requires Caution
Two legitimate orders can both be worth 100. SUM(DISTINCT total_amount) would include 100 only once and understate revenue. Use DISTINCT only when the metric truly calls for unique numeric values. It is not a safe repair for duplicate rows introduced by an incorrect join.
Summing Calculated Expressions
SELECT SUM(quantity * unit_price) AS gross_sales
FROM order_items;
The multiplication runs for each row, then SUM adds those line values. For quantities 2, 3, and 1 with prices 100, 50, and 200, the total is 200 + 150 + 200 = 550.
Discounts and Tax
SELECT SUM(
quantity * unit_price
- COALESCE(discount_amount, 0)
) AS net_sales
FROM order_items;
Without COALESCE, a NULL discount can make the entire row expression NULL and cause that line to be excluded from SUM. Whether NULL means no discount or unknown discount must be established by the data model.
SELECT SUM(amount + tax_amount) AS total_with_tax
FROM invoices;
Financial calculations should define whether values are stored at line or document level, when rounding occurs, and whether taxes are included, excluded, or recoverable.
Percentage Discounts
SELECT SUM(
price * quantity
* (1 - discount_percent / 100.0)
) AS net_sales
FROM order_items;
The decimal literal 100.0 helps avoid integer-style division in systems where operand types affect division. Production systems should use exact decimal types and a documented rounding rule at the correct business level.
Conditional SUM with CASE
SELECT SUM(
CASE
WHEN status = 'Completed' THEN total_amount
ELSE 0
END
) AS completed_sales
FROM orders;
Each completed order contributes its amount; other rows contribute zero. Conditional SUM is useful when several related totals must be calculated from one shared input set.
Multiple Conditional Totals
SELECT COALESCE(SUM(total_amount), 0) AS total_sales,
COALESCE(SUM(CASE
WHEN status = 'Completed' THEN total_amount ELSE 0
END), 0) AS completed_sales,
COALESCE(SUM(CASE
WHEN status = 'Pending' THEN total_amount ELSE 0
END), 0) AS pending_sales,
COALESCE(SUM(CASE
WHEN status = 'Cancelled' THEN total_amount ELSE 0
END), 0) AS cancelled_sales
FROM orders;
The categories can overlap if their conditions are not mutually exclusive. A dashboard should explain whether the components are expected to reconcile to the overall total.
Conditional SUM for Counting
SELECT SUM(
CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END
) AS failed_tests
FROM test_execution;
Each failed row adds one, so SUM acts as a conditional counter. COUNT(CASE WHEN status = 'FAILED' THEN 1 END) is another common form. Wrap SUM in COALESCE when an empty input should produce zero.
Today's and Yesterday's Sales
SELECT COALESCE(SUM(total_amount), 0) AS todays_sales
FROM orders
WHERE created_at >= CURRENT_DATE
AND created_at < CURRENT_DATE + INTERVAL 1 DAY;
SELECT COALESCE(SUM(total_amount), 0) AS yesterday_sales
FROM orders
WHERE created_at >= CURRENT_DATE - INTERVAL 1 DAY
AND created_at < CURRENT_DATE;
Half-open ranges handle timestamp precision correctly. The date boundaries use the database session zone unless the application calculates business-zone boundaries explicitly.
Rolling and Calendar Periods
SELECT COALESCE(SUM(total_amount), 0) AS rolling_sales
FROM orders
WHERE created_at >= NOW() - INTERVAL 7 DAY;
This is a rolling seven-day window, not seven complete calendar dates. A fixed monthly report should use the first day of the month and the first day of the next month.
SELECT COALESCE(SUM(total_amount), 0) AS september_sales
FROM orders
WHERE created_at >= '2026-09-01'
AND created_at < '2026-10-01';
Daily, Monthly, and Yearly Totals
SELECT DATE(order_date) AS sales_date,
SUM(total_amount) AS total_sales
FROM orders
WHERE order_date >= :report_start
AND order_date < :report_end
GROUP BY DATE(order_date)
ORDER BY sales_date;
Filtering first restricts the indexed range; grouping then creates report buckets. For monthly history, group by a year-month key so months from different years are not merged.
SELECT DATE_FORMAT(order_date, '%Y-%m') AS sales_month,
SUM(total_amount) AS total_sales
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
ORDER BY sales_month;
Quarterly Totals
SELECT YEAR(order_date) AS sales_year,
QUARTER(order_date) AS sales_quarter,
SUM(total_amount) AS total_sales
FROM orders
GROUP BY YEAR(order_date), QUARTER(order_date)
ORDER BY sales_year, sales_quarter;
Including the year prevents quarter 1 from every year being combined. Fiscal quarters may not match calendar quarters and often require a calendar dimension.
Customer Lifetime Sales
SELECT customer_id,
SUM(total_amount) AS lifetime_sales
FROM orders
WHERE status = 'Completed'
GROUP BY customer_id;
This is a simple revenue measure, not necessarily full customer lifetime value. Returns, taxes, costs, refunds, and currency conversion may need to be incorporated before the metric supports business decisions.
SUM() with JOIN
SELECT o.order_id,
SUM(i.quantity * i.unit_price) AS order_total
FROM orders o
JOIN order_items i
ON i.order_id = o.order_id
GROUP BY o.order_id;
The query has one joined row per order item and one output row per order. The sum therefore aggregates item values at the order grain.
Including Customers with No Orders
SELECT c.customer_id,
c.customer_name,
COALESCE(SUM(o.total_amount), 0) AS total_spent
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name;
The LEFT JOIN preserves customers without orders. Their right-side amounts are NULL, SUM has no numeric value to add, and COALESCE presents zero.
Join Multiplication and Double Counting
A major SUM error occurs when multiple one-to-many relationships are joined at once. If an order has three items and two payment rows, joining orders to both can produce six combinations. Item amounts and payments are then repeated.
Never use SUM(DISTINCT amount) as a general correction. Equal amounts can belong to different legitimate records. Fix the result grain instead.
Aggregate Before Joining
SELECT o.order_id,
COALESCE(i.item_total, 0) AS item_total,
COALESCE(p.payment_total, 0) AS payment_total
FROM orders o
LEFT JOIN (
SELECT order_id,
SUM(quantity * unit_price) AS item_total
FROM order_items
GROUP BY order_id
) i ON i.order_id = o.order_id
LEFT JOIN (
SELECT order_id,
SUM(amount) AS payment_total
FROM payments
GROUP BY order_id
) p ON p.order_id = o.order_id;
Each derived table produces at most one row per order before the join. Matching grains prevents the Cartesian multiplication of independent child collections.
SUM() Versus Other Aggregates
| Function | Question | Example result |
|---|---|---|
COUNT() | How many? | 3 orders |
SUM() | How much in total? | 600 total value |
AVG() | What is the average? | 200 average value |
MIN() | What is the smallest? | 100 |
MAX() | What is the largest? | 300 |
With values 100, 200, and 300, COUNT returns 3 and SUM returns 600. COUNT measures records or values; SUM adds the values themselves.
SUM() with a Subquery
SELECT SUM(customer_total) AS total_revenue
FROM (
SELECT customer_id,
SUM(total_amount) AS customer_total
FROM orders
GROUP BY customer_id
) AS customer_sales;
The outer sum equals the source total only if every source amount belongs to exactly one inner group and no later filter changes the set. The layered form is useful when intermediate customer totals are needed for additional logic.
Running Total with SUM()
SELECT order_id,
order_date,
total_amount,
SUM(total_amount) OVER (
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS running_total
FROM orders;
Unlike GROUP BY, the window function preserves every order row. The explicit ROWS frame accumulates from the first row through the current row, and the order identifier resolves equal dates deterministically.
Running Total by Customer
SELECT customer_id,
order_id,
order_date,
total_amount,
SUM(total_amount) OVER (
PARTITION BY customer_id
ORDER BY order_date, order_id
ROWS UNBOUNDED PRECEDING
) AS customer_running_total
FROM orders;
PARTITION BY restarts the accumulation for each customer. This supports account statements, customer history, inventory movement, and cumulative test execution time.
Aggregate SUM Versus Window SUM
An aggregate query with GROUP BY returns one row per customer. A window SUM can retain one row per order while adding the customer total to every row.
SELECT customer_id,
order_id,
total_amount,
SUM(total_amount) OVER (
PARTITION BY customer_id
) AS customer_total
FROM orders;
Choose according to required output grain, not just the function name.
Percentage of Total
SELECT product_id,
sales_amount,
100.0 * sales_amount
/ NULLIF(SUM(sales_amount) OVER (), 0)
AS percentage_of_total
FROM product_sales;
The window total provides one denominator across the result. NULLIF prevents division by zero. Define rounding and ensure that the displayed percentages may not add to exactly 100 after independent rounding.
Budget, Payroll, and Inventory
SELECT department,
SUM(expense_amount) AS total_expenses,
SUM(salary) AS payroll
FROM department_finance
GROUP BY department;
Only add values that share compatible meaning, unit, currency, and period. Inventory rows may represent current snapshots or movement transactions; summing snapshots across dates usually produces a meaningless total unless the metric explicitly calls for inventory-days.
Banking Net Amounts
SELECT account_id,
SUM(CASE
WHEN transaction_type = 'DEPOSIT' THEN amount
WHEN transaction_type = 'WITHDRAWAL' THEN -amount
ELSE 0
END) AS net_amount
FROM transactions
WHERE status = 'POSTED'
GROUP BY account_id;
The sign convention converts positive stored transaction amounts into net movement. Production banking balances may also include opening balance, reversals, holds, effective dates, and ledger rules.
Software Testing and API Usage
SELECT test_suite,
SUM(execution_seconds) AS total_execution_seconds,
SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END)
AS failed_tests
FROM test_execution
GROUP BY test_suite;
SELECT endpoint,
SUM(response_bytes) AS total_response_bytes
FROM api_logs
WHERE request_time >= NOW() - INTERVAL 24 HOUR
GROUP BY endpoint;
These examples total duration and data transfer while preserving clear units. Convert large byte totals to display units only after aggregation to avoid repeated row-level rounding.
Numeric Precision for Financial Totals
Use exact DECIMAL or NUMERIC types for monetary values where supported. Binary floating-point can represent many decimal fractions only approximately, so adding thousands of values can expose rounding differences.
Choose precision and scale for maximum expected values, taxes, rates, and aggregate growth. Summing millions of valid row values can exceed the range anticipated for one row, so inspect the DBMS return type and downstream application type.
Rounding Policy
Rounding each line and then summing can differ from summing unrounded values and rounding once. Both can be valid under different accounting or tax rules.
-- Sum rounded lines
SUM(ROUND(quantity * unit_price, 2))
-- Round the final sum
ROUND(SUM(quantity * unit_price), 2)
Do not choose between them for technical convenience. Follow the documented financial rule and retain enough precision for audit and reconciliation.
Currency and Unit Safety
Adding USD, EUR, and INR values directly produces a number with no valid currency meaning. Group by currency or convert through an approved exchange rate and effective date before totaling.
SELECT currency_code,
SUM(amount) AS currency_total
FROM payments
GROUP BY currency_code;
The same rule applies to seconds versus milliseconds, bytes versus kilobytes, and quantities measured in different units.
SUM() Performance
Performance depends on rows scanned, filters, indexes, joins, grouping cardinality, partitioning, numeric types, and execution plan. An index can locate a date range efficiently, but the database still needs to read qualifying values to calculate an exact total unless a maintained summary is available.
Use EXPLAIN, filter unnecessary rows early, avoid accidental join multiplication, and aggregate at the needed grain. For repeated large reports, consider summary tables or analytical storage with explicit freshness and reconciliation policies.
Transaction Visibility
A SUM reflects rows visible to the current transaction and isolation level. Concurrent committed or uncommitted changes can make two sessions observe different totals. Financial and compliance reports should identify a cutoff or run under a consistent snapshot so totals can be reproduced.
Maintaining Precomputed Totals
Systems sometimes persist account balances, cart totals, daily revenue, or usage counters. Reads become fast, but every insert, update, cancellation, refund, and retry must adjust the summary exactly once.
Use transactions or idempotent event processing and periodically reconcile summaries to authoritative detail rows. A cached total without a freshness timestamp can be more misleading than a slower exact query.
Negative Values, Refunds, and Reversals
SUM includes negative values naturally. Whether refunds should be stored as negative amounts, separate positive refund rows, or status-bearing transactions is a modeling decision. The query must follow the chosen convention consistently.
SELECT SUM(CASE
WHEN entry_type = 'SALE' THEN amount
WHEN entry_type = 'REFUND' THEN -amount
ELSE 0
END) AS net_revenue
FROM ledger_entries
WHERE posted_at >= :period_start
AND posted_at < :period_end;
Filtering out refunds before summing sales produces gross revenue, not net revenue. Name each metric explicitly so report readers know whether taxes, discounts, returns, chargebacks, and fees are included.
NULL Inside a Calculated Expression
SUM ignores a NULL expression, but that does not mean it safely handles every NULL component. In quantity * unit_price - discount, a NULL discount makes the entire expression NULL in ordinary SQL arithmetic. The line then contributes nothing, including its quantity and price.
SELECT SUM(
quantity * unit_price
- COALESCE(discount, 0)
) AS net_total
FROM order_items;
Use COALESCE only when the model defines missing discount as no discount. If NULL means the discount has not been calculated, silently replacing it with zero would conceal incomplete data.
Weighted Totals and Weighted Averages
SUM is the foundation of weighted calculations. Suppose a product has quantities purchased at different prices. A simple average of price rows ignores quantity, while a weighted average uses total value divided by total units.
SELECT SUM(quantity * unit_price)
/ NULLIF(SUM(quantity), 0)
AS weighted_average_price
FROM order_items;
The numerator and denominator must use the same filtered population and compatible units. Protect the denominator from zero and choose decimal types that preserve required precision.
Opening Balance Plus Movement
A period balance often combines an opening value with transactions inside the period rather than summing every historical row for every report.
SELECT a.opening_balance
+ COALESCE(SUM(t.signed_amount), 0)
AS closing_balance
FROM account_period a
LEFT JOIN transactions t
ON t.account_id = a.account_id
AND t.posted_at >= a.period_start
AND t.posted_at < a.period_end
WHERE a.account_id = :account_id
GROUP BY a.account_id, a.opening_balance;
Reconcile the closing balance to the next period's opening balance. Posting date, effective date, reversal rules, and transaction status must follow the ledger policy.
Window Frames Matter
A window ORDER BY without an explicit frame can use a database-specific default such as a RANGE frame. When several rows share the same ordering value, peers may receive the same cumulative total rather than advancing one row at a time.
SUM(total_amount) OVER (
ORDER BY order_date, order_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)
Specify ROWS and a deterministic tie-breaker when the requirement is a row-by-row running total. Use an intentional RANGE frame when peer values should advance together.
Moving Totals
Window frames can calculate a moving total over a fixed number of preceding rows.
SELECT sales_date,
daily_sales,
SUM(daily_sales) OVER (
ORDER BY sales_date
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS seven_row_sales
FROM daily_sales;
This is seven rows, not necessarily seven calendar dates. Missing dates change its meaning. Join to a calendar table and fill absent dates with zero when the business requirement is a seven-calendar-day total.
Allocation and Reconciliation
Allocating one total across departments, products, or cost centers can create rounding differences. If each allocated line is rounded independently, their sum may differ by a cent from the source amount.
Define a residual policy, such as assigning the rounding remainder to the largest allocation or a designated line. Then verify that SUM(allocated_amount) equals the source total for every allocation group.
Overflow and Aggregate Return Types
A column type that safely stores one value may not safely store the sum of millions of values. Database systems can widen aggregate types, raise an overflow error, or apply vendor-specific return rules. Application drivers can introduce another limit when mapping the result.
Test totals near expected maximum volume, inspect the DBMS documentation for SUM return types, and use explicit casts only when the target type is intentionally large enough. Never cast to a smaller type merely to satisfy application code.
SUM with Boolean-Like Conditions
Some databases allow a boolean expression to participate directly in numeric arithmetic, while others do not. A query such as SUM(status = 'FAILED') is therefore not portable. CASE communicates the conversion explicitly.
SUM(CASE WHEN status = 'FAILED' THEN 1 ELSE 0 END)
The explicit form is easier to review and behaves consistently across major SQL systems.
Summary Tables and Materialized Views
Daily or monthly totals can be pre-aggregated into a summary table or materialized view. This reduces repeated scans of detailed transactions and is valuable for historical dashboards.
The summary must define refresh timing, late-arriving data, correction handling, and reconciliation. If yesterday's transaction is corrected today, an incremental process must update the historical bucket rather than only the current day.
Cross-Database Portability
Basic aggregate and window SUM syntax is widely supported, but return types, integer overflow behavior, decimal widening, FILTER clauses, frame defaults, interval syntax, and materialized-view features vary. Some systems also expose different behavior for summing native interval or boolean types.
When migrating a query, compare results for empty input, all-NULL input, extreme values, decimal scale, negative values, tied window ordering, and join plans. Matching syntax is not enough; the numeric and transactional semantics must also match.
Auditability of Important Totals
A business-critical total should be explainable from its source rows. Record the reporting period, filters, currency, status rules, time zone, cutoff timestamp, query or report version, and data snapshot. Provide drill-down identifiers when practical so reviewers can reconcile the aggregate to individual transactions.
Reproducibility is especially important for payroll, tax, settlement, billing, and compliance reports. A number without its calculation policy can be impossible to verify even when the SQL addition itself is correct.
Common Mistakes
- Using SUM on nonnumeric concepts.
- Confusing the number of rows with the total of their values.
- Forgetting that an empty or all-NULL input returns NULL.
- Replacing meaningful unknown values with zero without a policy.
- Using SUM(DISTINCT) to hide duplicate rows from a join.
- Allowing one-to-many joins to multiply amounts.
- Mixing aggregate and nonaggregate columns at incompatible grains.
- Using approximate floating-point types for exact money.
- Applying the wrong rounding level.
- Adding values with different currencies or units.
- Using integer division in percentages.
- Omitting a deterministic frame and order in running totals.
Testing SUM Queries
Test empty input, all-NULL input, zero, positive and negative values, duplicates, very large values, maximum decimal scale, and cancellation totals. For calculated expressions, include NULL operands, zero quantities, discounts, taxes, and rounding boundaries.
For joins, test parents with no children, one child, multiple children, and multiple independent child collections. For windows, test tied sort values and partition changes. Reconcile a small hand-calculated dataset before measuring production performance.
Practical Checklist
- Define the entity and unit being totaled.
- Use an exact numeric type when exact totals matter.
- Apply row filters in WHERE before aggregation.
- Use GROUP BY for one total per category.
- Use HAVING for aggregate thresholds.
- Decide whether empty input should remain NULL or display zero.
- Handle NULL operands in calculated expressions deliberately.
- Do not use SUM(DISTINCT) to repair a bad join.
- Aggregate child tables to the correct grain before joining.
- Define currency, unit, sign, and rounding policies.
- Use stable ordering and explicit frames for running totals.
- Verify execution plans, snapshots, and summary freshness.
Interview Questions and Answers
What is SUM()? It is an aggregate function that adds non-NULL numeric expression values.
Does SUM include NULL? No. NULL inputs are ignored, but an empty or all-NULL aggregate generally returns NULL.
How do you return zero when no values exist? Use COALESCE(SUM(expression), 0) when zero matches the business meaning.
SUM versus COUNT? SUM adds values; COUNT counts rows or non-NULL values.
Can SUM total expressions? Yes, for example SUM(quantity * unit_price).
WHERE versus HAVING? WHERE filters source rows; HAVING filters groups based on aggregate results.
Why can joins produce incorrect totals? Multiple matches can repeat a value across joined rows. Aggregate at compatible grains before joining.
How do you calculate a running total? Use SUM as a window function with deterministic ORDER BY and an explicit frame.
Why avoid floating-point money? Binary floating-point can introduce approximation; exact decimal types better preserve monetary values.
Mental Model
For values 100, 200, NULL, 300, and 400, SUM(amount) returns 1,000. NULL is ignored. COUNT(amount) returns 4, AVG(amount) returns 250, MIN(amount) returns 100, and MAX(amount) returns 400.
| Function | Meaning | Result |
|---|---|---|
COUNT(amount) | Known values | 4 |
SUM(amount) | Total value | 1000 |
AVG(amount) | Average known value | 250 |
MIN(amount) | Smallest known value | 100 |
MAX(amount) | Largest known value | 400 |
Key Takeaway
SUM() answers "what is the total?" It can add a column, a calculated expression, conditional values, grouped values, or an ordered window of values.
RELIABLE SUM = CORRECT RESULT GRAIN
+ COMPATIBLE UNITS
+ EXACT NUMERIC TYPES
+ INTENTIONAL NULL POLICY
+ CORRECT FILTERS
+ JOIN-SAFE AGGREGATION
+ GOVERNED ROUNDING
+ RECONCILIATION TESTS
Use WHERE to choose source rows, GROUP BY to establish categories, HAVING to filter aggregate groups, CASE for conditional totals, and window SUM for running or partitioned analysis. Most incorrect totals come not from addition itself but from unclear meaning, duplicated join rows, incompatible units, and untested precision rules.