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_idsalary
150000
260000
370000
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

FunctionQuestionExample 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

  1. Using SUM on nonnumeric concepts.
  2. Confusing the number of rows with the total of their values.
  3. Forgetting that an empty or all-NULL input returns NULL.
  4. Replacing meaningful unknown values with zero without a policy.
  5. Using SUM(DISTINCT) to hide duplicate rows from a join.
  6. Allowing one-to-many joins to multiply amounts.
  7. Mixing aggregate and nonaggregate columns at incompatible grains.
  8. Using approximate floating-point types for exact money.
  9. Applying the wrong rounding level.
  10. Adding values with different currencies or units.
  11. Using integer division in percentages.
  12. 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

  1. Define the entity and unit being totaled.
  2. Use an exact numeric type when exact totals matter.
  3. Apply row filters in WHERE before aggregation.
  4. Use GROUP BY for one total per category.
  5. Use HAVING for aggregate thresholds.
  6. Decide whether empty input should remain NULL or display zero.
  7. Handle NULL operands in calculated expressions deliberately.
  8. Do not use SUM(DISTINCT) to repair a bad join.
  9. Aggregate child tables to the correct grain before joining.
  10. Define currency, unit, sign, and rounding policies.
  11. Use stable ordering and explicit frames for running totals.
  12. 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.

FunctionMeaningResult
COUNT(amount)Known values4
SUM(amount)Total value1000
AVG(amount)Average known value250
MIN(amount)Smallest known value100
MAX(amount)Largest known value400

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.