SQL SUM

The SQL SUM() Function

The SUM() function yields the cumulative sum of a numerical column.

Example

Retrieve the total sum of all Quantity values in the OrderDetails table.

SELECT SUM(Quantity)
FROM OrderDetails;

Syntax

SELECT SUM(column_name)

FROM table_name

WHERE condition;

Demo Database

Here is an excerpt from the OrderDetails table used in the examples:

OrderDetailID	OrderID	ProductID	Quantity
1	10248	11	12
2	10248	42	10
3	10248	72	5
4	10249	14	9
5	10249	51	40

Add a WHERE Clause

You have the option to include a WHERE clause to define specific conditions.

Example

Retrieve the total sum of the Quantity field for the product with ProductID 11.

SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductId = 11;

Use an Alias

Assign a name to the summarized column using the AS keyword.

Example

Name the column as “total”.

SELECT SUM(Quantity) AS total
FROM OrderDetails;

Use SUM() with GROUP BY

In this scenario, we utilize the SUM() function alongside the GROUP BY clause to retrieve the Quantity for each OrderID listed in the OrderDetails table:

Example

SELECT OrderID, SUM(Quantity) AS [Total Quantity]
FROM OrderDetails
GROUP BY OrderID;

Further explanation regarding the GROUP BY clause will be provided later in this tutorial.

SUM() With an Expression

The parameter within the SUM() function can also be an expression.

For instance, if we assume each product in the OrderDetails column costs $10, we can determine the total earnings in dollars by multiplying each quantity by 10.

Example

the SUM() function to include an expression within it.

SELECT SUM(Quantity * 10)
FROM OrderDetails;

We can additionally connect the OrderDetails table with the Products table to determine the precise amount, rather than making the assumption of $10.

Example

Combine the OrderDetails table with the Products table through a join operation, and then utilize the SUM() function to calculate the total amount.

SELECT SUM(Price * Quantity)
FROM OrderDetails
LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID;