SQL SUM() Function

The SUM() function in SQL is an aggregate function that calculates the total sum of a numeric column. It is commonly used with the GROUP BY clause to get the sum for each group or without it to get the sum of all rows.

Basic Syntax

SELECT SUM(column_name) FROM table_name WHERE condition;

Examples

Example 1: Simple Sum

Consider a table Sales with the following data:

To get the total sales amount:

SELECT SUM(Amount) AS TotalSales FROM Sales;

Output:

Example 2: Sum with Condition

To get the total sales amount for product 'A':

SELECT SUM(Amount) AS TotalSalesForA FROM Sales WHERE Product = 'A';

Output:

Example 3: Sum with Group By

To get the total sales amount for each product:

SELECT Product, SUM(Amount) AS TotalSales FROM Sales GROUP BY Product;

Output:

Notes

• The SUM() function ignores NULL values.
• It can be used in conjunction with other aggregate functions like AVG(), COUNT(), etc.
• It can be used in HAVING clauses to filter groups based on aggregate values.

Example 4: Sum with HAVING

To get the products with total sales greater than 400:

SELECT Product, SUM(Amount) AS TotalSales FROM Sales GROUP BY Product HAVING SUM(Amount) > 400;

Output:

These examples illustrate how the SUM() function can be used to calculate the total of a numeric column under various conditions and groupings.