MySQL SUM function

The SUM() function displays the total addition of the table values. It supports the arithmetic operation of the column values. This function does not return a null value. Here, the sum function shows the summation of all values in the table. If the given table shows empty or null, then the function returns a null value. This function works on numerical data type values of the table.

Syntax

The SUM () function basic syntax shows below.

SUM(function_expression);

MySQL SUM () function syntax with table shows below.

SELECT SUM(function_expression) FROM Table_Name WHERE condition;

Description

• The SUM() function requires the column data for addition.
• The "function_expression" represents the table column for the SUM() function.
• The "WHERE" clause applies conditions on the sum() function.

Prerequisite for the SUM function

• Create a table with the required column name and its other constraints.

mysql> create table marksheet (  

    id int NOT NULL,

    name varchar(100) NOT NULL,    

    marks int,  

    birth_date date,

    phone_number bigint  

    );  

• Insert the value in the table as per column and its data type.

mysql> insert into 

marksheet(id, name, marks, birth_date, phone_number) 

VALUES    

(1, 'Sunny', 78, '2000-07-01', 9123011122),  

(2, 'Pummy', 74, '2000-09-25', 9823013126),  

(3, 'merry', 71, '2000-04-11', 9183032125),  

(4, 'merry', 85, '2000-10-08', 7113019962),

(5, 'merry', 71, '2000-08-11', 8745232100),

(6, 'jenny', 79, '2000-01-17', 772310032);

• You can refer to the table structure and information using the output query.

mysql> SELECT * FROM marksheet; 

Here, you see the five columns and six rows in the table. This table contains int, bigint, date, and varchar data type's value.

Examples of the SUM function

1) Example: The "SUM" function query uses for the summation of the single column. Here, the "SUM" function adds all data of the row and returns the output.  

mysql> SELECT SUM(marks) AS Mark  FROM marksheet;

 Output

This output image displays the addition of the "marks" column. The output image displays a single column and row with value.

2) Example: This example will use the "SUM" function on the multiple columns of the table. This aggregate function returns the addition of the values at the respective column.

mysql> SELECT SUM(marks) AS Mark, SUM(id) AS ID, SUM(birth_date) AS date  FROM marksheet;

Output

The above image shows the addition of the marks, id, and date columns. Here, the SUM function operates on the entire rows means six rows of the columns.

3) Example: Execute the sum function to show the summation of the required column using the WHERE clause. Here, the aggregation function uses marks, id, and birth_date columns. This function uses the "WHERE" clause with the "less than" condition.

mysql> SELECT SUM(marks) AS Mark, SUM(id) AS ID, SUM(birth_date) AS date  FROM marksheet WHERE id < 4;

Output

The above output image shows the summation of the numerical and date columns. The table uses the "WHERE" clause to operate less than the "4" value. You can show the addition of the conditional row data of the columns.

4) Example: This example explains the "SUM" function with the "GROUP BY" clause. Here, this aggregation function operates multiple columns for the addition operation. The "SUM" function uses the "GROUP BY" clause on the "marks" column.

mysql> SELECT SUM(marks) AS Mark, SUM(id) AS ID, SUM(birth_date) AS date  FROM marksheet  GROUP BY marks;

Output

The above output image shows the summation of the numerical columns. The table use addition operation on similar value like 71 + 71 = 142 value.

5) Example: The SUM function with the "HAVING" clause example shows below.

Execute the "SUM" function to perform the addition of the multiple columns. Here, the SUM function uses the "GROUP BY" and "HAVING" clauses. This clause works on the "marks" column of the table.

mysql> SELECT SUM(marks) AS Mark, SUM(id) AS ID, SUM(birth_date) AS date  FROM marksheet  GROUP BY marks 
HAVING SUM(marks) < 78;

Output

The sum function returns three columns and one row in the above output table. The marks, id, and date show numerical values. The above table shows values less than the "78" marks column.

6) Example: The SUM function with the "DISTINCT" clause example shows below. The DISTINCT clause is used to remove duplicate data.

mysql> SELECT SUM( DISTINCT marks) AS Mark, SUM( DISTINCT id) AS ID, SUM( DISTINCT birth_date) AS date  FROM marksheet  GROUP BY marks 
HAVING SUM(marks) < 75;

Output

The sum function returns the above output. This output shows three columns and one row. The date shows in the numerical format.

7) Example: The SUM functions with multiple clauses example shows below.

This example uses the SUM function with the DISTINCT clause. The "DISTINCT" clause works with the marks, id, and birth_date column. The "WHERE", "GROUP BY," and "HAVING" clauses works on the "marks" column.

mysql> SELECT SUM( DISTINCT marks) AS Mark, SUM( DISTINCT id) AS ID, SUM( DISTINCT birth_date) AS date  FROM marksheet  
WHERE id < 5
GROUP BY marks 
HAVING SUM(marks) < 75;

Output

The above output returns the unique value of the table column. This output shows the marks, id, and date column with the sum function's data. This table shows two rows and three columns of the marksheet table.

8) Example: The SUM functions with the condition example shows below.

The SUM function uses the "marks", "id," and "birth_date" columns of the table.

mysql> SELECT SUM(marks) AS Mark, SUM(id) AS ID, SUM(birth_date) AS date  FROM marksheet  
WHERE id < 5
GROUP BY marks 
HAVING SUM(marks) < 75;

Output

The output image shows the value of the column data. This output does not show the summation of the column data. This output shows two rows of data because of the "HAVING" condition.