SQL SUM

What is the SQL SUM function?

Returns the total of a numeric expression for all selected rows or for each group in a GROUP BY clause.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL SUM: Supported by PostgreSQL, MySQL, MariaDB, SQL Server, Oracle, SQLite, Snowflake, Redshift, BigQuery, DB2, Teradata, and all other ANSI-compliant databases

SQL SUM Full Explanation

SUM is a standard SQL aggregate function that adds together all non-NULL values of a numeric expression. When used without GROUP BY, it returns a single scalar value representing the grand total of the result set. When used with GROUP BY, it computes a separate subtotal for each group. NULL values are ignored. DISTINCT can be specified to add only unique values; ALL (the default) aggregates every non-NULL value. SUM works on integer, decimal, and floating-point types and can also operate on interval types in databases that support them. If the data set is empty or all evaluated values are NULL, the result is NULL, not zero. Because SUM is an aggregate, it cannot be used in a WHERE clause; use HAVING to filter aggregated results. Some systems allow SUM() OVER() window syntax to return running totals without collapsing rows.

SQL SUM Syntax

SELECT SUM([DISTINCT | ALL] numeric_expression)
FROM table_name
[WHERE condition]
[GROUP BY grouping_columns];

SQL SUM Parameters

DISTINCT (ALL) - Keyword|||DISTINCT sums only unique non-NULL values; ALL (default) includes all non-NULL values
numeric_expression (Numeric) - Column name or arithmetic expression to aggregate

Example Queries Using SQL SUM

-- 1. Grand total of sales
SELECT SUM(total_amount) AS total_sales
FROM orders;

-- 2. Monthly sales subtotals
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(total_amount) AS monthly_sales
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

-- 3. Sum of distinct invoice amounts
SELECT SUM(DISTINCT total_amount) AS unique_revenue
FROM invoices;

-- 4. Running total (window function)
SELECT order_id,
       order_date,
       total_amount,
       SUM(total_amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

Expected Output Using SQL SUM

Each query returns a single numeric column containing the computed total or subtotal
For window functions, the running_total column shows an accumulating sum per row

Use Cases with SQL SUM

Calculate total revenue, cost, or quantity
Generate subtotals per customer, product, or period
Derive rolling or cumulative sums with window functions
Validate data integrity by comparing sums across tables

Common Mistakes with SQL SUM

Summing non-numeric data types
Forgetting GROUP BY when selecting additional columns
Expecting SUM to treat NULL as zero (NULLs are ignored)
Using SUM in WHERE instead of HAVING
Assuming SUM returns zero for empty result sets

First Introduced In

SQL-92 Standard

Frequently Asked Questions

What data types can SUM handle?

SUM works on integer, decimal, numeric, float, real, and in some systems interval types. It ignores non-numeric types.

Why is SUM returning NULL instead of 0?

If every evaluated row is NULL or the result set is empty, SUM returns NULL. Coalesce the result (e.g., COALESCE(SUM(col),0)) if you need zero.

Can I use SUM in a WHERE clause?

No. Aggregates are evaluated after WHERE. Use HAVING to filter on SUM results.

How do I combine SUM with other columns?

Include a GROUP BY listing every non-aggregated column, or use a window function to avoid grouping.