MySQL sum() function
The MySQL sum() function is used to return the total summed value of an expression. It returns NULL if the result set does not have any rows. It is one of the kinds of aggregate functions in MySQL.
Syntax
Following are the syntax of sum() function in MySQL:
Parameter Explanation
aggregate_expression: It specifies the column or expression that we are going to calculate the sum.
table_name: It specifies the tables from where we want to retrieve records. There must be at least one table listed in the FROM clause.
WHERE conditions: It is optional. It specifies the conditions that must be fulfilled for the records to be selected.
MySQL sum() function example
Consider our database has a table named employees, having the following data. Now, we are going to understand this function with various examples:
1. Basic Example
Execute the following query that calculates the total number of working hours of all employees in the table:
Output:
We will get the result as below:
2. MySQL sum() function with WHERE clause
This example is used to return the result based on the condition specified in the WHERE clause. Execute the following query to calculate the total working hours of employees whose working_hours >= 12.
Output:
This statement will give the output as below:
3. MySQL sum() function with GROUP BY clause
We can also use the SUM() function with the GROUP BY clause to return the total summed value for each group. For example, this statement calculates the total working hours of each employee by using the SUM() function with the GROUP BY clause, as shown in the following query:
Output:
Here, we can see that the total working hours of each employee calculates by grouping them based on their occupation.
4. MySQL sum() function with HAVING clause
The HAVING clause is used to filter the group with the sum() function in MySQL. Execute the following statement that calculates the working hours of all employees, grouping them based on their occupation and returns the result whose Total_working_hours>24.
Output:
5. MySQL sum() function with DISTINCT clause
MySQL uses the DISTINCT keyword to remove the duplicate rows from the column name. This clause can also be used with sum() function to return the total summed value of a Unique number of records present in the table.
Execute the following query that removes the duplicate records in the working_hours column of the employee table and then calculates the sum:
Output:
