sql_func_sum

SQL SUM() Function

The SUM() Function

The SUM() function is an aggregate function that calculates the total sum of values in a numeric column. It’s essential for tasks like calculating total sales, inventory counts, or any other cumulative measure.

Key characteristics of SUM():

• It operates on a single column containing numeric data.
• It ignores NULL values in the calculation. If all values in the column for a group are NULL, SUM() returns NULL.
• It is often used with the GROUP BY clause to calculate sums for different categories.

SQL SUM() Syntax

SELECT SUM(numeric_column_name) FROM table_name WHERE condition;

numeric_column_name: The column whose values you want to sum.table_name: The table containing the column.condition: An optional clause to filter rows before summing.

Demo Database

For our examples, we’ll use a conceptual ‘OrderDetails’ table, similar to what you might find in the Northwind sample database or any e-commerce system.

Here’s a sample structure and data from an “OrderDetails” table:

OrderDetailID (INT, Primary Key)
OrderID (INT, Foreign Key)
ProductID (INT, Foreign Key)
Quantity (INT)
UnitPrice (DECIMAL)

-- Sample Data --
1, 10248, 11, 12, 14.00
2, 10248, 42, 10, 9.80
3, 10248, 72, 5, 34.80
4, 10249, 14, 9, 18.60
5, 10249, 51, 40, 42.40

SQL SUM() Example: Total Quantity Ordered

The following SQL statement calculates the total sum of all quantities from the “Quantity” column in the “OrderDetails” table:

SELECT SUM(Quantity) AS TotalItemsOrdered
FROM OrderDetails;

This query would return a single row with a single column, TotalItemsOrdered, showing the sum of all values in the Quantity column (e.g., 12 + 10 + 5 + 9 + 40 = 76 based on the sample data).

SQL SUM() with GROUP BY Example: Total Quantity Per Order

To find the total quantity of items for each individual order, you can use SUM() in conjunction with the GROUP BY clause:

SELECT OrderID, SUM(Quantity) AS TotalQuantityPerOrder
FROM OrderDetails
GROUP BY OrderID;

This query would return:

OrderID | TotalQuantityPerOrder
------- | ---------------------
10248   | 27
10249   | 49

Practical Application

The SUM() function is widely used in reporting and analytics:

• Calculating total revenue by summing sales amounts.
• Aggregating total units sold for products or categories.
• Summing up expenses for financial analysis.
• Calculating total scores in a gaming application.

Further Learning:

To understand how to filter groups after aggregation, explore the SQL HAVING clause.