VMware ESXi and vSphere Cluster Management

SQL SUM() Function

Learn how to use SQL SUM() to add numeric values, filter totals with WHERE, group results with GROUP BY, handle NULL values, and sum expressions.

SUM() is a SQL aggregate function that adds numeric values from multiple rows. It is useful for calculating totals such as album sales, quantities, balances, order amounts, and revenue.

An aggregate function derives a summary value from a set of rows. Without grouping, SUM() normally returns one total for the selected rows.

What SUM() Does

A database table contains rows and columns. A column is a named field with a defined data type, and a table is a collection of related rows and columns. When you use SUM() with a numeric column, SQL adds the usable values in that column.

For example, if an album table has sell values of 5, 8, and 10, the sum is 23. The result is a total across those rows, not the value from one individual row.

SUM() is intended for numeric data, including integers, decimal prices, quantities, balances, sales counts, and monetary amounts. The input can be a numeric column or a numeric expression.

Basic SUM() Syntax

SELECT SUM(column_name) AS total_value
FROM table_name;

• SELECT chooses the value or expression to return.
• SUM(column_name) is the aggregate expression that adds the column values.
• AS total_value assigns a readable alias to the result column.
• FROM table_name identifies the source table.

Because this query has no GROUP BY clause, it normally returns one column and one row containing the overall total.

Example: Total Album Sales

Suppose an album table contains these relevant rows:

album_id | title          | artist         | sell
---------+----------------+----------------+-----
1        | First Record   | Example Artist | 5
2        | Second Record  | Example Artist | 8
3        | Another Album  | Other Artist   | 10
4        | Unreported     | Other Artist   | NULL

The numeric sales values add up to 23. The NULL value is not a numeric zero and is not added as a value.

SELECT SUM(sell) AS total_sales
FROM album;

Result:

total_sales
-----------
23

The returned value means that the combined known sell values across the album rows equal 23. It does not mean that every album sold 23 copies, and it is not the value of a particular row.

An alias such as total_sales makes the output easier to understand than a database-generated label such as SUM(sell).

Filtering Rows Before Summing

WHERE restricts the rows included in a query. The filtering happens before SUM() calculates its total.

SELECT SUM(sell) AS total_sales
FROM album
WHERE artist = 'Example Artist';

Only rows whose artist is Example Artist contribute to the total. With the sample data, the result is 13 because 5 + 8 = 13.

You can filter by dates, categories, status values, or numeric conditions in the same way:

SELECT SUM(sell) AS total_sales
FROM album
WHERE sell >= 10;

When checking an unexpected total, first inspect the rows selected by the condition:

SELECT album_id, title, artist, sell
FROM album
WHERE sell >= 10;

Using SUM() with GROUP BY

GROUP BY divides rows into groups and calculates an aggregate result for each group. Instead of one grand total, you receive one total per artist.

SELECT artist, SUM(sell) AS total_sales
FROM album
GROUP BY artist;

With the sample data, the result is conceptually:

artist         | total_sales
---------------+------------
Example Artist | 13
Other Artist   | 10

The NULL sales value for Other Artist is ignored. The grouped result includes the artist column and that artist's total.

When an aggregate query selects a non-aggregate column, such as artist, that column generally must appear in GROUP BY:

SELECT artist, SUM(sell) AS total_sales
FROM album
GROUP BY artist;

If you select artist with SUM(sell) but omit GROUP BY artist, many SQL databases report a grouping error because SQL cannot determine which single artist should label the grand total.

NULL Values and Empty Results

NULL represents a missing or unknown value. It is different from zero. SUM() ignores NULL values rather than adding them as numeric values.

If at least one non-NULL value is available, SQL sums those values. If every selected value is NULL, or if no rows match the filter, SUM() commonly returns NULL rather than zero.

SELECT SUM(sell) AS total_sales
FROM album
WHERE release_year = 1900;

If no album matches that year, the total may be NULL. Use COALESCE() when the required business result is zero:

SELECT COALESCE(SUM(sell), 0) AS total_sales
FROM album
WHERE release_year = 1900;

COALESCE() returns its first non-NULL argument. In this example, it returns the sum when a sum exists and returns 0 when the sum is NULL.

Summing an Expression

SUM() can aggregate a calculated numeric expression, not only a single column. For an order_items table with quantity and unit_price, calculate total revenue like this:

SELECT SUM(quantity * unit_price) AS total_revenue
FROM order_items;

For each row, SQL calculates quantity * unit_price, then SUM() adds those row-level results.

The input must produce a suitable numeric value. Avoid summing textual data such as numbers stored in a character column. Use a properly typed numeric column, or use a database-appropriate conversion only after validating that the stored text is valid numeric data. Conversion syntax differs between database systems.

SUM() Compared with Other Aggregate Functions

• SUM() adds numeric values.
• COUNT() counts rows or, when given a column, counts non-NULL values; it does not add the values.
• AVG() calculates an average of numeric values.
• MIN() returns the smallest value.
• MAX() returns the largest value.

DISTINCT can be used with SUM() when the requirement is specifically to total unique numeric values:

SELECT SUM(DISTINCT sell) AS total_unique_sales_values
FROM album;

This removes duplicate numeric values before summing. It does not remove duplicate rows. For example, two albums that each have a sell value of 10 contribute only one 10 to SUM(DISTINCT sell). Use ordinary SUM(sell) unless totaling each unique value once is intentional.

Common Problems and Fixes

The total is NULL instead of zero

The filter may match no rows, or every included sell value may be NULL. Wrap the aggregate with COALESCE() when zero is the desired result.

SELECT COALESCE(SUM(sell), 0) AS total_sales
FROM album
WHERE artist = 'Missing Artist';

One grand total appears instead of totals per artist

A query without GROUP BY produces one overall total. Select the grouping column and add it to GROUP BY:

SELECT artist, SUM(sell) AS total_sales
FROM album
GROUP BY artist;

A grouping error appears

A selected non-aggregate column such as artist was probably not included in GROUP BY. Add the column to GROUP BY, or remove it from SELECT if you want only one grand total.

The total is higher or lower than expected

Unexpected rows, an incorrect WHERE condition, a wrong numeric column, or a join that duplicates rows can change the result. Inspect the contributing rows with a non-aggregate query and check join cardinality before aggregating.

SUM() fails for the selected field

The field may be textual rather than numeric, or it may contain values that cannot be safely converted. Store quantities and amounts in appropriate numeric data types whenever possible.

SUM(DISTINCT value) is smaller than expected

DISTINCT removes repeated numeric values before summing. Replace it with ordinary SUM(value) unless the requirement is to total unique values only once.

Quick Reference

-- Overall total
SELECT SUM(numeric_column) AS total_value
FROM table_name;

-- Total after filtering rows
SELECT SUM(numeric_column) AS total_value
FROM table_name
WHERE condition;

-- One total per group
SELECT group_column, SUM(numeric_column) AS total_value
FROM table_name
GROUP BY group_column;

-- Return zero instead of NULL
SELECT COALESCE(SUM(numeric_column), 0) AS total_value
FROM table_name;

For more practice with this aggregate, return to the SQL SUM() Function reference and compare overall, filtered, and grouped totals.