SUM() Function in MySQL
The SUM() function in MySQL is a powerful aggregate function used to calculate the total sum of values in a numeric column. By summing up the values in the specified column, this function helps in generating overall totals and performing calculations that provide meaningful insights from our data.
In this article, We will learn about SUM() Function in MySQL in detail by understanding various examples and so on.
MySQL SUM() Function
- The
SUM()function in MySQL is used to calculate the total sum of a numeric column. - It aggregates the values of the specified column and returns the result.
- This function is often used in queries to get the total of column values and optionally apply conditions or group the results.
Features of SUM() Function in MySQL
- This function is used to compute the sum of the specified group of values.
- This function comes under Numeric Functions.
- This function accepts only one parameter namely expression.
- This function ignores the null value.
Syntax:
SUM(expression)Explanation:
This method accepts only one parameter as given below:
- expression: A specified expression which can either be a field or a given formula.
Examples of SUM() Function in MySQL
To understand the SUM() Function in MySQL we will use the below table called sales defined below:
Table: sales
| order_id | product_name | quantity | price_per_unit |
|---|---|---|---|
| 1 | Widget A | 10 | 15.00 |
| 2 | Widget B | 5 | 20.00 |
| 3 | Widget A | 7 | 15.00 |
| 4 | Widget C | 3 | 25.00 |
| 5 | Widget B | 2 | 20.00 |
Example 1: Basic Example
To calculate the total quantity of products sold:
SELECT SUM(quantity) AS total_quantity FROM sales;Output:
| total_quantity |
|---|
| 27 |
Explanation: The SUM(quantity) function calculates the total sum of the quantity column across all rows in the sales table, which is 27 (10 + 5 + 7 + 3 + 2).
Example 2: SUM() Function with WHERE Clause
To calculate the total quantity of "Widget A" sold:
SELECT SUM(quantity) AS total_widget_a_quantity
FROM sales
WHERE product_name = 'Widget A';
Output:
| total_widget_a_quantity |
|---|
| 17 |
Explanation: The SUM(quantity) function, combined with the WHERE clause, calculates the total quantity of "Widget A". The rows with "Widget A" have quantities of 10 and 7, so the total is 17.
Example 3: SUM() Function with GROUP BY Clause
To calculate the total quantity of each product sold:
SELECT product_name, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_name;
Output:
| product_name | total_quantity |
|---|---|
| Widget A | 17 |
| Widget B | 7 |
| Widget C | 3 |
Explanation: The SUM(quantity) function, combined with the GROUP BY clause, calculates the total quantity for each product. It groups the results by product_name and sums up the quantities within each group.
Example 4: SUM() Function with HAVING Clause
To calculate the total quantity of products and show only those with a total quantity greater than 10:
SELECT product_name, SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_name
HAVING SUM(quantity) > 10;
Output:
| product_name | total_quantity |
|---|---|
| Widget A | 17 |
Explanation: The SUM(quantity) function, combined with GROUP BY and HAVING clauses, calculates the total quantity for each product and then filters the results to include only those with a total quantity greater than 10. Only "Widget A" meets this criterion.
Example 5: SUM() Function with DISTINCT Clause
To calculate the total quantity of distinct products sold:
SELECT SUM(DISTINCT quantity) AS total_distinct_quantity
FROM sales;
Output:
| total_distinct_quantity |
|---|
| 25 |
Explanation: The SUM(DISTINCT quantity) function calculates the sum of unique quantities from the quantity column. The distinct quantities are 10, 5, 7, and 3. The sum of these distinct values is 25 (10 + 5 + 7 + 3).
Conclusion
The SUM() function in MySQL is a versatile tool for aggregating numeric data. It can be used to calculate totals in various contexts, including basic aggregations, conditional summations with WHERE, grouped results with GROUP BY, filtered groups with HAVING, and distinct value calculations with DISTINCT. Mastering these applications of SUM() helps in effectively analyzing and summarizing data in SQL queries.
