Back to Library

SUM() in SQL

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

Syntax

SUM([DISTINCT] expression)
Return type
Same as input type (NUMERIC, DECIMAL, INTEGER, etc.)

SUM() Function Example

SELECT SUM(salary) FROM employees;
-- Output: 1250000 (total of all salaries)

What is SUM() in SQL?

The SUM() function in SQL calculates the total sum of all values in a specified column or expression. It ignores NULL values and is commonly used for financial calculations, sales reports, and aggregating numeric data.

This function is supported in SQL Server, MySQL, PostgreSQL, and Oracle.

The SUM() function is widely used in data aggregation, financial reporting, and business analytics.

Parameters:

  • expression: Column name or numeric expression to sum

  • DISTINCT (optional): Sum only unique values

Example Use Cases:

-- Calculate total revenue

SELECT SUM(amount) AS total_revenue FROM sales; 
-- Output:
period       amount      total_revenue
--------     --------    -------------
Q1 2024      25,750.00
Q2 2024      31,200.00
Q3 2024      28,450.00
Q4 2024      35,600.00
                        -------------
                        $121,000.00

-- Sum inventory value 

SELECT SUM(quantity * unit_price) AS inventory_value FROM products; 
-- Output:
category      quantity    unit_price    inventory_value
---------     --------    ----------    ---------------
Electronics      100        299.99         29,999.00
Clothing         250         49.99         12,497.50
Books            500         19.99          9,995.00
Sports           150         89.99         13,498.50
                                      ---------------
Total Inventory Value:                   $65,990.00

-- Total unique payments

SELECT SUM(DISTINCT payment_amount) AS unique_payments FROM transactions;
-- Output:

payment_amount    unique_payments
--------------    ---------------
100.00
100.00            (counted once)
250.00
300.00
300.00            (counted once)
450.00
                  ---------------
                    $1,100.00

Notes:

  • Behavior: Ignores NULL values in calculation

  • Performance Considerations: Efficiently optimized in most DBMS

  • Version Info: Core aggregation function available in all major DBMS

  • Deprecated/Recommended Alternatives: None

Error Handling:

  • Error: Returns NULL if all values are NULL

  • Recommendation: Use COALESCE to handle NULL results


Supported Databases:

DBMS
Function / Syntax
Example
Behavior with NULL
MySQL

SUM([DISTINCT] expr)

SUM(salary)

Ignores NULL

PostgreSQL

SUM([DISTINCT] expression)

SUM(salary)

Ignores NULL

SQL Server

SUM([DISTINCT] expression)

SUM(salary)

Ignores NULL

SQLite

SUM([DISTINCT] X)

SUM(salary)

Ignores NULL

BigQuery

SUM([DISTINCT] expression)

SUM(salary)

Ignores NULL

Snowflake

SUM([DISTINCT] expression)

SUM(salary)

Ignores NULL

Athena

SUM([DISTINCT] expression)

SUM(salary)

Ignores NULL

Related Functions:

STDEV()

Calculates the standard deviation (population or sample) of a set of numeric values.

Calculates the standard deviation (population or sample) of a set of numeric values.

Calculates the standard deviation (population or sample) of a set of numeric values.

Calculates the standard deviation (population or sample) of a set of numeric values.

VARIANCE()

Calculates the statistical variance (population or sample) of a set of numeric values.

Calculates the statistical variance (population or sample) of a set of numeric values.

Calculates the statistical variance (population or sample) of a set of numeric values.

Calculates the statistical variance (population or sample) of a set of numeric values.

STRING_AGG()

Concatenates values from multiple rows into a single string, with specified delimiter.

Concatenates values from multiple rows into a single string, with specified delimiter.

Concatenates values from multiple rows into a single string, with specified delimiter.

Concatenates values from multiple rows into a single string, with specified delimiter.

GROUP_CONCAT()

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

Concatenates values from multiple rows into a single string, with optional delimiter and ordering.

SUM()

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

Calculates the total sum of all values in a specified column or expression, ignoring NULL values.

One place for all your queries,
directly on your SQL editor