TheDeveloperBlog.com

Home | Contact Us

C-Sharp | Java | Python | Swift | GO | WPF | Ruby | Scala | F# | JavaScript | SQL | PHP | Angular | HTML

MySQL sum()

MySQL sum() function example with examples on CRUD, insert statement, select statement, update statement, delete statement, use database, keys, joins etc.

<< Back to MYSQL

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:

SELECT SUM(aggregate_expression)  
FROM tables  
[WHERE conditions];

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:

mysql sum()

1. Basic Example

Execute the following query that calculates the total number of working hours of all employees in the table:

mysql> SELECT SUM(working_hours) AS "Total working hours" FROM employees;

Output:

We will get the result as below:

mysql sum()

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.

mysql> SELECT SUM(working_hours) AS "Total working hours" FROM employees WHERE working_hours>=12;

Output:

This statement will give the output as below:

mysql sum()

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:

mysql> SELECT emp_id, emp_name, occupation, SUM(working_hours) AS "Total working hours" FROM employees GROUP BY occupation;

Output:

Here, we can see that the total working hours of each employee calculates by grouping them based on their occupation.

mysql sum()

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.

mysql> SELECT emp_id, emp_name, occupation, 
SUM(working_hours) Total_working_hours 
FROM employees 
GROUP BY occupation 
HAVING SUM(working_hours)>24;

Output:

mysql sum()

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:

mysql> SELECT emp_name, occupation, 
SUM(DISTINCT working_hours) Total_working_hours
FROM employees 
GROUP BY occupation;

Output:

mysql sum()
Next TopicMySQL average()




Related Links:


Related Links

Adjectives Ado Ai Android Angular Antonyms Apache Articles Asp Autocad Automata Aws Azure Basic Binary Bitcoin Blockchain C Cassandra Change Coa Computer Control Cpp Create Creating C-Sharp Cyber Daa Data Dbms Deletion Devops Difference Discrete Es6 Ethical Examples Features Firebase Flutter Fs Git Go Hbase History Hive Hiveql How Html Idioms Insertion Installing Ios Java Joomla Js Kafka Kali Laravel Logical Machine Matlab Matrix Mongodb Mysql One Opencv Oracle Ordering Os Pandas Php Pig Pl Postgresql Powershell Prepositions Program Python React Ruby Scala Selecting Selenium Sentence Seo Sharepoint Software Spellings Spotting Spring Sql Sqlite Sqoop Svn Swift Synonyms Talend Testng Types Uml Unity Vbnet Verbal Webdriver What Wpf