EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials MySQL Tutorial MySQL sum()
 

MySQL sum()

Aanchal Sharma
Article byAanchal Sharma
EDUCBA
Reviewed byRavi Rathore

Updated June 6, 2023

MySQL sum()

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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:

MySQL sum() - 1

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:

MySQL sum() - 2

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:

MySQL sum() - 3

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:

MySQL sum() - 4

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:

MySQL sum() - 5

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:

MySQL sum() - 6

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:

ORDER BY

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:

NULL

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:

NULL

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:

Products

Suppliers – Code #2

select * from suppliers;

Output:

Suppliers

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:

MySQL sum() - 12

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()

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA Login

Forgot Password?

🚀 Limited Time Offer! - 🎁 ENROLL NOW