SUM() in SQL
Calculates the total sum of all values in a specified column or expression, ignoring NULL values.
Syntax
Return type
SUM() Function Example
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
-- Sum inventory value
-- Total unique payments
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:
COUNT()
Counts the number of rows or non-null values in a specified column. COUNT(*) counts all rows including nulls, while COUNT(column) counts non-null values in the specified column.
AVG()
Calculates the arithmetic mean (average) of all non-null values in a specified column or expression.
MIN()
Returns the smallest value from a specified column or expression, ignoring NULL values.
MAX()
Returns the largest value from a specified column or expression, ignoring NULL values.