SQL Sum
The SQL SUM() Function
The total sum of a numeric column is returned by the SUM() function.
Example
Return the sum of all Quantity fields in the OrderDetails table:
SELECT SUM(Quantity)
FROM OrderDetails;
Syntax
SELECT SUM(column_name)
FROM table_name
WHERE condition;
Demo Database
| 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
A WHERE clause can be added to establish conditions:
Example
Return the sum of the Quantity field for the product with ProductID 11:
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductId = 11;
Use an Alias
Use the AS keyword to assign a name to the summary column.
Example
Name the column “total”:
SELECT SUM(Quantity) AS total
FROM OrderDetails;
Use SUM() with GROUP BY
Here, we retrieve the Quantity for each OrderID in the OrderDetails table using the GROUP BY clause and the SUM() function:
Example
SELECT OrderID, SUM(Quantity) AS [Total Quantity]
FROM OrderDetails
GROUP BY OrderID;
SUM() With an Expression
The SUM() function’s parameter may alternatively be an expression.
By multiplying each amount by 10, we can determine the total earnings in dollars, assuming that each product in the OrderDetails column costs ten dollars:
Example
Use an expression inside the SUM() function:
SELECT SUM(Quantity * 10)
FROM OrderDetails;
Alternatively, rather than presuming the amount is $10, we can link the OrderDetails table to the Products table to get the real amount:
Example
Join OrderDetails with Products, and use SUM() to find the total amount:
SELECT SUM(Price * Quantity)
FROM OrderDetails
LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID;
