MySQL SUM

Syntax of MySQL SUM() Function

The syntax of MySQL SUM() Function is as follows:

Parameters of MySQL SUM() Function

The MySQL SUM() Function takes one parameter as given below:

• expression:
  It is an expression that can either be a field/Column_name or a given formula.

Return Value of MySQL SUM() Function

The MySQL SUM() Function returns the sum of all the values present in the specified column:

• The data type of the return value is the same as the data type of the input column provided to the SUM() Function.
• If the input column contains no data, then the function returns NULL.

Exceptions of MySQL SUM() Function

MySQL SUM() Function has the following exceptions:

• If the specified column has NULL values, the MySQL SUM() function will return NULL.
• If the input column contains any non-numeric data, you will receive an error message indicating that the argument is not valid.

How does the MySQL SUM() Function Work?

The MySQL SUM() function works by adding up all the values from the input column. It adds up everything by iterating over each row in the given table and adding each value of the input column to a total. Once all rows have been iterated over, the total sum is returned.

We can also use the SUM() function with the GROUP BY and HAVING clauses in MySQL to perform aggregate addition of specific columns based on certain conditions.

Examples

Now we have learned about the SUM() function in MySQL, So we can apply it in some of our examples.

Example - 1: Basic Usage of SUM() Function

The below Code is a basic example of how to use the MySQL SUM() function to add up the values of the specified column.

Here is a breakdown of what each part of the code does:

• Create a new table named MyTable with a single column value of type integer using the CREATE TABLE statement.
• Insert five rows of data into the MyTable using the INSERT statement. Each row contains a single integer value ranging from 1 to 5.
• Use the SELECT statement with the SUM function to calculate the sum of all the values in the value column of the MyTable table. The resulting sum is aliased as SUM using the AS keyword.

In summary, this code creates a simple table with integer values and then uses the SUM function to calculate the sum of those values. The resulting sum is then displayed in the output of the SELECT statement.

Using the above code, the table we are working on will look like this:

Query:

Output:

The output of the above code is a single row with a single column. The column is labeled SUM and contains the sum of all the values in the value column of the table.

Example - 2: Using the SUM() Function to Add Float Values

This code is an example of how to use the MySQL SUM() function to sum up float values in the table. Here is a breakdown of what each part of the code does:

• Create a new table named MyTable with a single column price of type float using the CREATE TABLE statement.
• Insert five rows of data into the table using the INSERT statement. Each row contains a single float value representing a price.
• Use the SELECT statement with the SUM function to calculate the sum of all the values in the price column of the MyTable table. The resulting sum is aliased as Total Price using double quotes.

In summary, this code creates a table of prices and then uses the SUM function to calculate the total price of all the prices. The resulting total price is then displayed in the output of the SELECT statement with the field label Total Price.

Using the above code, the table we are working on will look like this:

Query:

Output:

The output of the code above will be a single row with a single column labeled Total Price. The value of this column will be the sum of all the prices in the table

Example - 3: Using the SUM() with WHERE Clause

This code is an example of how to use the MySQL SUM() function with a WHERE clause to filter data from a table based on certain conditions. Here is a breakdown of what each part of the code does:

• Create a new table named MyTable with two columns: category of type char(10) and price of type float using the CREATE TABLE statement.
• Inserts five rows of data into the MyTable table using the INSERT statement. Each row contains a category (either "A" or "B") and a corresponding float value representing a price.
• Uses the SELECT statement with the SUM function to calculate the sum of all the values in the price column of the table where the category column equals "A". The resulting sum is aliased as Total Price using double quotes.

In summary, this code creates a table of prices with categories and then uses the SUM function to calculate the total price of all the prices in the price column of the MyTable table where the category column equals "A". The resulting total price is then displayed in the output of the SELECT statement with the column label Total Price.

Using the above code, the table we are working on will look like this:

Query:

Output:

The final output of the code above will be a single row with a single column labeled Total Price. The value of this column will be the sum of all the prices in the price column of the MyTable table where the category column equals "A".

Example - 4: Using the SUM() to Calculate Average of Values

This code is an example of how to use the MySQL SUM() function to calculate the average of the values. Here is a breakdown of what each part of the code does:

• Create a new table named MyTable with two columns: category of type char(10) and price of type float using the CREATE TABLE statement.
• Insert five rows of data into the MyTable table using the INSERT statement. Each row contains a category (either "A" or "B") and a corresponding float value representing a price.
• Use the SELECT statement with the SUM and COUNT functions to calculate the average of all the values in the price column of the MyTable table. The SUM function adds up all the prices, and the COUNT function counts the number of rows in the table. The resulting sum is divided by the number of rows to get the average. The resulting average is aliased as Average using double quotes.

In summary, this code creates a table of prices with categories and then uses the SUM and COUNT functions to calculate the average price of all the prices in the price column of the MyTable table. The resulting average is then displayed in the output of the SELECT statement with the label Average.

Using the above code, the table we are working on will look like this:

Query:

Output :

The output of the code above will be a single row with a single column labeled Average. The value of this column will be the average of all the prices in the price column of the table:

Example - 5: Using the SUM() with GROUP BY and HAVING

This code is an example of how to use the MySQL SUM() function with GROUP BY and HAVING clauses. Here is a breakdown of what each part of the code does:

• Create a table named MyTable with two columns: category and price. Then insert five rows into this table for these columns.
• Then use the SELECT statement with the SUM() function to calculate the total price for each category in the table. It is done by grouping the rows by the category column using the GROUP BY clause.
• HAVING clause is used to filter the groups based on the aggregated values calculated by the SUM() function. In this case, the condition will return the groups where the total price is greater than 30.0 will be returned in the result set.

In summary, this code creates a table of prices with categories and then uses the SUM() function with GROUP BY and HAVING Clauses to filter and show the result based on the conditions. The resulting table is then displayed in the output of the SELECT statement with the columns as category and Total Price.

Using the above code, the table we are working on will look like this:

Query:

Output:

The output of this code will show the total price for each category where the total price is greater than 30.0.

See Also

• SQL INSERT INTO Statement
• WHERE Clause in SQL
• SQL GROUP BY Clause