SQL SUM() Function

The SQL SUM() function is an aggregate function used to calculate the total value of a numeric column. It is widely used in reporting, financial calculations, and data analysis to quickly get overall totals.

• Returns a single total of selected numeric values.
• Works with WHERE, GROUP BY, and HAVING for conditional sums.
• Supports expressions and DISTINCT to sum unique values only.

Example: First, we create a demo SQL database and table, on which we use the SUM() functions.

Query:

SELECT SUM(Amount) AS TotalAmount
FROM Orders;

Output:

• SUM(Amount) calculates the total of all values present in the Amount column.
• The query returns one result named TotalAmount from the Orders table.

Syntax:

SELECT SUM(column_name)
FROM table_name;

Examples of SQL SUM() Function

In this section, we demonstrate the SUM() function using a Sales table (Product, Quantity, Price) to understand how it calculates totals and distinct sums; consider this Sales table for the below examples:

Example 1: Using SUM() with One Column

In this example, SUM() calculates the total of a single numeric column.

Query:

SELECT SUM(Price) AS TotalPrice
FROM Sales;

Output:

• Adds all values in the Price column.
• Returns the result as TotalPrice.

Example 2: Using SUM() with an Expression

In this example, calculates total revenue by multiplying quantity and price.

Query:

SELECT SUM(Quantity * Price) AS TotalRevenue
FROM Sales;

Output:

• Multiplies Quantity × Price for each row.
• Returns the total revenue as TotalRevenue.

Example 3: Using SUM() with GROUP BY

In this example, SUM() is used along with GROUP BY clause. It calculates revenue separately for each product.

Query:

SELECT Product, SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY Product;

Output:

• Calculates revenue for each product.
• Groups rows by Product before summing.

Example 4: Using SUM() with DISTINCT

In this example, SUM() is used with the DISTINCT keyword to sum only unique price values.

Query:

SELECT SUM(DISTINCT Price) AS SumDistinctPrice
FROM Sales;

Output:

• Adds only unique Price values.
• Returns the result as SumDistinctPrice.

Example 5: Using SUM() with HAVING

The HAVING clause can be used with GROUP BY to filter groups based on the result of the SUM() function, allowing conditions on aggregated data.

Query:

SELECT Product, SUM(Quantity * Price) AS TotalRevenue
FROM Sales
GROUP BY Product
HAVING SUM(Quantity * Price) > 2000;

Output:

• Filters products after grouping.
• Shows only products with revenue greater than 2000.