SQL online course

SQL SUM() Function

Learn how to use SQL SUM() to total numeric values, filter rows with WHERE, group totals with GROUP BY, and filter groups with HAVING.

The SQL SUM() function calculates the total of numeric values. It is an aggregate function, which means it performs a calculation across multiple rows and returns a summarized result.

SUM() is useful for totaling quantities, prices, sales amounts, account balances, inventory values, and other numeric measures. It operates on a numeric column or another numeric expression in a table.

If you are new to SQL, review SQL SELECT statements and the basics of SQL functions first.

Basic SUM() syntax

SELECT SUM(column_name)
FROM table_name;

SUM(column_name) is the aggregate calculation. The function argument, column_name, should identify the numeric column whose values you want to total.

Without a GROUP BY clause, this query normally returns one aggregate value, even when many source rows are included.

Example table

The examples use an album table with columns such as:

  • album_id: an integer identifier for each album
  • album_name: the album's name
  • artist_name: the artist or group
  • genre: the music category
  • sell: a numeric sales value, stored as an integer or decimal

For example, the sell column might contain values of 40, 75, 120, and 65. SUM() can add these values to produce 300.

Total all album sales

To calculate one total across every album row, pass the sell column to SUM():

SELECT SUM(sell) AS total_sales
FROM album;

Assuming the included sales values add up to 300, the result is one row:

total_sales
-----------
300

The query reads as: select the sum of the sell values from the album table. Because there is no grouping, all qualifying rows contribute to one overall total.

Naming the SUM() result with an alias

A database may generate a label such as SUM(sell) for the result column. An alias is a temporary, readable name assigned to a query result. Use AS to create one:

SELECT SUM(sell) AS total_sales
FROM album;

Here, total_sales is the alias. Aliases make result sets easier to read and are especially useful in reports or application code. See SQL aliases for more examples.

Data types accepted by SUM()

SUM() is intended for numeric data. Common suitable types include:

  • Integer types, such as INTEGER or BIGINT
  • Decimal or fixed-point types, such as DECIMAL or NUMERIC
  • Other database-specific numeric types

Text columns cannot ordinarily be meaningfully summed. If numbers are stored as character data, use a properly typed numeric column when possible. If conversion is necessary, use the conversion syntax supported by your database and ensure every value is valid numeric data.

How SUM() handles NULL

NULL represents a missing or unknown value. SUM() generally ignores NULL inputs while adding the non-NULL numeric values.

For example, if sell contains 40, NULL, and 60, the result is normally 100 rather than NULL. The NULL row does not contribute a value to the total.

If no qualifying non-NULL numeric values are available, SUM() can return NULL. This commonly occurs when the table has no rows, a filter excludes every row, or all matching values are NULL. The precise behavior and display can vary slightly by database system, but applications should account for a possible NULL result.

Use COALESCE when the required output is zero instead of NULL:

SELECT COALESCE(SUM(sell), 0) AS total_sales
FROM album;

COALESCE returns its first non-NULL value. Therefore, it returns the SUM() result when one exists and returns 0 when SUM() returns NULL.

Filtering rows before summing with WHERE

WHERE filters source rows before the aggregate calculation occurs. Use it when only certain rows should contribute to the total.

For example, this query totals sales for albums in the Rock genre:

SELECT SUM(sell) AS rock_sales
FROM album
WHERE genre = 'Rock';

Only rows whose genre is 'Rock' are passed to SUM(). Rows from other genres are excluded before the total is calculated. You can also filter by a date range, status, artist, or other row-level condition. Learn more in SQL WHERE clause.

Totals by category with GROUP BY

To calculate a separate total for each category, use GROUP BY. GROUP BY divides rows into groups based on one or more columns, and SUM() calculates a value within each group.

This query returns total sales for each artist:

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

A possible result is:

artist_name  total_sales
-----------  -----------
Artist A     115
Artist B     185

This is different from an overall total. The ungrouped query returns one row for the entire table. The grouped query returns one row for each distinct artist_name.

When selecting a grouped column with an aggregate, include that grouping column in GROUP BY. In the example, artist_name identifies the groups and SUM(sell) calculates each group's total.

Filtering grouped totals with HAVING

HAVING filters groups after aggregate calculations have been made. Use it when the condition refers to SUM() or another aggregate function.

To find artists whose total sales exceed 100:

SELECT artist_name,
       SUM(sell) AS total_sales
FROM album
GROUP BY artist_name
HAVING SUM(sell) > 100;

The database first forms an artist group and calculates each group's SUM(). It then retains only groups whose total is greater than 100.

The key distinction is:

  • WHERE filters individual source rows before grouping and aggregation.
  • HAVING filters groups after aggregation.

Therefore, a condition such as genre = 'Rock' belongs in WHERE, while a condition such as SUM(sell) > 100 belongs in HAVING.

SUM() query patterns

  • Overall total: SELECT SUM(value_column) FROM table_name;
  • Filtered total: SELECT SUM(value_column) AS total_name FROM table_name WHERE condition;
  • Total per group: SELECT group_column, SUM(value_column) AS total_name FROM table_name GROUP BY group_column;
  • Groups above a threshold: SELECT group_column, SUM(value_column) AS total_name FROM table_name GROUP BY group_column HAVING SUM(value_column) > threshold;
  • Zero for a missing total: SELECT COALESCE(SUM(value_column), 0) AS total_name FROM table_name;

Common problems and troubleshooting

The query returns NULL instead of zero

No qualifying non-NULL numeric values may be available. Wrap SUM() in COALESCE when the application or report requires zero:

SELECT COALESCE(SUM(sell), 0) AS total_sales
FROM album;

The query fails because the target column is text

SUM() requires numeric input, but the selected column may use a character type. Prefer a numeric column. If the text contains valid numbers, convert it using your database's supported conversion syntax, and handle invalid values before summing.

The total is larger than expected

Review the rows included by the query. A missing or incorrect WHERE condition can include unwanted records. Joins can also duplicate source rows, causing the same sales value to be added more than once. Inspect the joined result before applying SUM().

The query returns several totals instead of one

A GROUP BY clause creates one result per distinct group. Remove GROUP BY when you need one overall total, or keep it when separate totals are intended.

A SUM() condition does not work in WHERE

WHERE runs before SUM() is calculated, so it cannot be used to filter an aggregate result in the usual grouped query. Move the condition to HAVING:

SELECT artist_name, SUM(sell) AS total_sales
FROM album
GROUP BY artist_name
HAVING SUM(sell) > 100;

Exam-relevant notes

  • SUM() is an aggregate function that returns the total of numeric values.
  • The argument is normally a numeric column or numeric expression.
  • Without GROUP BY, SUM() normally produces one aggregate result.
  • WHERE filters rows before aggregation.
  • GROUP BY produces a separate aggregate result for each group.
  • HAVING filters groups using aggregate results.
  • SUM() generally ignores NULL inputs, but can return NULL when no qualifying non-NULL values exist.
  • Use COALESCE(SUM(column_name), 0) when a missing total should be represented as zero.

Related aggregate functions

SUM() calculates a total. Other aggregate functions answer different questions: COUNT() counts rows or values, AVG() calculates an average, MAX() finds the greatest value, and MIN() finds the smallest value.