SQLSUM() Function
The SQL SUM() Function
TheSUM() function returns the total sum of a numeric column.
Example
Return the sum of allQuantity fields in theOrderDetails table:
FROM OrderDetails;
Syntax
SELECT SUM(column_name)
FROMtable_name
WHEREcondition;
Demo Database
Below is a selection from theOrderDetails 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 can add aWHERE clause to specify conditions:
Example
Return the sum of theQuantity field for the product withProductID 11:
FROM OrderDetails
WHERE ProductId = 11;
Use an Alias
Give the summarized column a name by using theAS keyword.
Use SUM() with GROUP BY
Here we use theSUM() function and theGROUP BY clause, to return theQuantityfor eachOrderID in the OrderDetails table:
Example
FROM OrderDetails
GROUP BY OrderID;
You will learn more about theGROUP BY clause later in this tutorial.
SUM() With an Expression
The parameter inside theSUM() function can also be an expression.
If we assume that each product in theOrderDetails column costs 10 dollars, we can find the total earnings in dollars by multiply each quantity with 10:
Example
Use an expression inside theSUM() function:
FROM OrderDetails;
We can also join theOrderDetails table to theProducts table to find the actual amount, instead of assuming it is 10 dollars:
Example
JoinOrderDetails withProducts, and useSUM() to find the total amount:
FROM OrderDetails
LEFT JOIN Products ON OrderDetails.ProductID = Products.ProductID;
You will learn more aboutJoins later in this tutorial.
