MySQL SUM() Function

The SUM() function in MySQL is used to calculate the total sum of a numeric column or expression.

Definition and Usage

• The SUM() function calculates the sum of a set of values, which can be from a column or any numerical expression.
• Note: NULL values are ignored in the sum calculation.

Syntax

SUM(expression)

Parameter Values

Technical Details

• Works in: From MySQL 4.0
• Return Value: A numeric value representing the sum of the specified values or expressions.

Examples

Example 1: Sum of the Quantity field in the OrderDetails table

SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;

Output (example):

TotalItemsOrdered
-----------------
500

In this example, the sum of the Quantity field from all records in the OrderDetails table is calculated.

Example 2: Sum of the Price field for products above a certain price

SELECT SUM(Price) AS TotalSales FROM Products WHERE Price > 100;

Output (example):

TotalSales
----------
2500

In this example, the sum of Price for products in the Products table with a price greater than 100 is calculated.

Example 3: Sum of values with conditions (ignores NULL values)

SELECT SUM(Discount) AS TotalDiscounts FROM Orders;

Output (example):

TotalDiscounts
---------------
300

In this example, if any Discount values are NULL, they are ignored in the summation.

Use Cases

• Sales Reports: Calculating the total sales or revenue by summing up the values from the relevant column.
• Inventory Management: Summing quantities to get total stock.
• Financial Reports: Summing expenses, incomes, or other financial metrics.

Related Functions

• AVG(): Returns the average of a set of values.
• COUNT(): Returns the number of records that match a given condition.
• MIN() and MAX(): Return the minimum and maximum values in a set of values, respectively.