MySQL sum()

Updated June 6, 2023

Introduction to MySQL sum()

MySQL SUM() is an aggregate function that calculates the sum of the provided set of values. The SUM() avoids the NULL values while evaluating the sum of the data values in MySQL.

As an aggregate function, it implements the sum calculation on multiple values and produces a distinct value. In MySQL, the aggregate functions are usually applied with the GROUP BY clause to calculate an aggregate result value for the specific values or group of values mentioned in the query.

Finding the sum of a specific expression may give a NULL value when no rows exist in the result set.

Syntax

We have the subsequent elementary syntax structure for the MySQL SUM() function:

SUM(Expr_Value)

Or

SUM(DISTINCT Expr_Value)

Here, let us explain the syntax terms:

• When you use the SELECT statement with the SUM() function, which produces no rows, the MySQL SUM() function returns a NULL value instead of zero.
• The option DISTINCT keyword is provided to instruct the MySQL SUM() function to evaluate the total unique values in the set.
• The MySQL SUM() function disregards the NULL values in the sum calculation in the server.
• Expr_Value can be any column name of the specific table. It can be one or multiple, separated by a comma.

How does the SUM() function works in MySQL?

You can implement the MySQL SUM() function through the SELECT statement and calculate it using JOIN clauses.

• We also use the MySQL SUM() function along with the WHERE clause to retrieve the result sum of a particular expression that is clarified against a condition positioned after the clause WHERE. This will help find the total values from a large data set in the database.
• Suppose we need to rotate table rows to table columns, then the SUM() function is used together with the MySQL CASE expression, which is a type of SUMIF logic illustrated below:

SELECT
SUM(CASE
WHEN expression…
END) ColumnName1,
SUM(CASE
WHEN expression…
END) ColumnName2,…..,
FROM TableName1 INNER JOIN TableName2 USING(ColumnName);

The syntax shows a CASE statement with the SUM() function evaluated for table columns done for the respective tables. The tables are also joined with INNER JOIN and apply the USING keyword to specify the table’s column name.

Since the SUM() is an aggregate function, it works on multiple table rows or a table column to find the sum result to return only a value.

Examples to Implement MySQL sum()

Let us evaluate the MySQL SUM() to show the sum working with the table columns and rows to get the result total set.

Example #1 – Simple Example

Let us take a table from the database as a demo table to generate the total sum of column table values.

We have a table named Books, having some fields defined with columns such as BookID, BookName, Language, and Price. To view the contents of this Books table, let us query the data:

Code #1

SELECT * FROM Books;

Output:

We are going to use the aggregate SUM() function to fetch the total price of the books from the table quantity using the query below:

Code #2

SELECT SUM(Price)Total_Price FROM Books;

Output:

As shown above, the sum calculated the book column’s total price.

We can also apply the DISTINCT keyword to find the unique sum value of unique column values. So, the SUM() function will ignore the duplicate and NULL values if available in the column values of the table.

Code #3

SELECT SUM(DISTINCT Price) Total_Price FROM Books;

Output:

It shows the same total because the Price column contains no duplicates.

Example #2 – With Expression

Suppose we have a table named Suppliers:

Code #1

select * from suppliers;

Output:

For supplier line items of supplier id 11, we have the following query:

Code #2

SELECT Category, Unit, CostEach FROM Suppliers WHERE Supplier_ID = 11;

Output:

Now, we will calculate the total for a supplier item with Supplier id 11 using MySQL SUM() function:

Code #3

SELECT SUM(Unit * CostEach)TotalCost FROM Suppliers WHERE Supplier_ID = 11;

Output:

Example #3 – With GROUP BY and ORDER BY clause

We will use the SUM() function with the GROUP BY clause to produce the sum output by grouping the values based on a specific table column. For example, using the SUM(), we will query the total price of each supplier item with the GROUP BY clause:

Code #1

SELECT Supplier_ID, SUM(Unit * CostEach) TotalCost FROM Suppliers GROUP BY Supplier_ID ORDER BY TotalCost DESC;

Output:

In this case, the result set groups by Supplier id and orders by the calculated total cost in descending order.

Example #4 – With clause HAVING

The HAVING clause uses the SUM() function to filter the sum group and ensures that the total cost amount is greater than a specific provided integer value, as follows:

Code #1

SELECT Supplier_ID, SUM(Unit * CostEach) TotalCost FROM Suppliers GROUP BY Supplier_ID
HAVING SUM(Unit * CostEach)>200 ORDER BY TotalCost DESC;

Output:

Example #5 – With NULL

If the result set is empty, then the SUM() returns a NULL value, but you can also show zero instead using the COALESCE() with two arguments:

Code #1

SELECT COALESCE (SUM(Unit * CostEach), 0) Output FROM Suppliers WHERE Supplier_ID = 15;

Output:

The result is zero because there is no row with supplier id 15 in the table Suppliers.

Example #6 – With JOIN clause

We will take two tables, Products and Suppliers, which:

Products – Code #1

select * from products;

Output:

Suppliers – Code #2

select * from suppliers;

Output:

Calculating sum by SUM() clause with JOIN clause as follows:

Code #3

SELECT SUM(Unit * CostEach) Product_cost FROM Suppliers INNER JOIN Products ON Supplier_ID WHERE Product_Name = 'Maggie';

Output:

The result sum value evaluates based on a condition provided by the values in the next table.

Conclusion

• The MySQL SUM() is similar to a mathematical sum calculation that finds the totality of provided table values in the database.
• The aggregate SUM() function provides a useful way to obtain a summarized data result set of integer data type values, particularly for column values in tables that contain any product records or eCommerce or business-related database.

Recommended Articles

We hope that this EDUCBA information on “MySQL sum()” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. MySQL GROUP_CONCAT()
2. MySQL ON DELETE CASCADE
3. REINDEX in MySQL
4. MySQL LAG()