MySQL sum()

Introduction to MySQL sum()

• MySQL SUM() is a MySQL aggregate function that calculates the sum of provided set of values. The SUM() avoids the NULL values while evaluating the sum of the data values in MySQL.
• As it is an aggregate function so it implements the sum calculation on multiple values and produces a distinct value.In MySQL, the aggregate functions are normally applied in combination with the GROUP BY clause so that is calculates an aggregate result value for the specific values or group of values mentioned in the query.
• If we are finding the sum of a specific expression, then it may give NULL value when there exists no rows 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 the SELECT statement is used with SUM() function that outputs no row, then the MySQL SUM() function will return NULL value but not zero.
• The option DISTINCT keyword is provided to instruct the MySQL SUM() function to evaluate the total of 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 comma.

How SUM() function works in MySQL?

The MySQL SUM() function is implemented with SELECT statement and  also can be calculated with JOIN clauses.

• We use the MySQL SUM() function along with WHERE clause also to retrieve the result sum of a particular expression that is clarified against a condition positioned after the clause WHERE. This will be helpful to find the sum total of values from a large set of data in the database.
• Suppose, if 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);

Here, the syntax shows a CASE statements with SUM() function evaluated for table columns done for the respective tables. The tables are also joined with INNER JOIN and applying the USING keyword to specifycolumn name of the table.

Since, the SUM() is an aggregate function so it works on multiple table rows or of 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 working of sum with the table columns and rows to get the result total set.

Example #1. Simple Example of MySQL SUM() function

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 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 has calculated the sum of total price from the book column.

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

Code #3

SELECT SUM(DISTINCT Price) Total_Price FROM Books;

Output:

It shows the same sum total because the Price column does not contain any duplicates.

Example #2. with expression

Suppose, we have a table named Suppliers:

Code #1

select * from suppliers;

Output:

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

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 clause and ORDER BY clause

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

Code #1

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

Output:

Here the result set is grouped by Supplier id and ordered by total cost calculated in descending order.

Example #4. with clause HAVING

The HAVING clause is used with the SUM() function to filter the sum group where the total cost amount is greater than certain 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 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 where:

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 is evaluated on the basis of a condition provided by the values in the next table.

Conclusion

• The MySQL SUM() is similar to a mathematical sum calculation that finds out the totality of provided table values in database.
• The aggregate SUM() function is useful to get the summarized data result set of integer data type values especially for those column values of tables containing any product records or eCommerce or business related database.

Recommended Articles

This is a guide to MySQL sum(). Here we discuss an introduction to MySQL sum(), syntax, parameters, how does it work with query examples. You can also go through our other related articles to learn more –

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