MySQL - SUM() Function

Quiz

The MySQL SUM() function is an aggregate function that is used to calculate the sum of all values in a particular column/field. If the specified row(s) doesn't exist this function returns NULL.

If you are working with the SUM() function in MySQL, make sure to remember the following points −

If you use the SUM() function in a SELECT statement that returns no row, the SUM() function returns NULL, not zero.
If you use the DISTINCT keyword, this function calculates and returns the sum of the unique values of the given column.
The SUM() function ignores the NULL values in the calculation.

Syntax

Following is the syntax of MySQL SUM() function −

SUM(expr);

Parameters

This method accepts a parameter. The same is described below −

expr: An expression or column for which we want to calculate the sum.

Return value

This method calculates and returns the sum of all values in a particular column/field.

Example

First of all, let us create a table named CUSTOMERS using the CREATE TABLE statement as follows −

CREATE TABLE CUSTOMERS (
   ID INT AUTO_INCREMENT,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);

Using the below query, we are inserting 7 records into the above created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );

To display all the records that are inserted, execute the following query −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Now, we are using the MySQL SUM() function to calculate the total SALARY of all the customers −

SELECT SUM(SALARY) From CUSTOMERS;

Output

This will produce the following result −

SUM(SALARY)
35000.00

Example

If we use the MySQL SUM() function on any column that returns no records (i.e., an empty result set), the SUM() function returns NULL, not zero −

SELECT SUM(SALARY) as TotalSalary
FROM CUSTOMERS
WHERE NAME = 'Varun';

Output

The above query will return NULL because customer 'Varun' does not exist.

TotalSalary
NULL

Example

In the following query, we are using the DISTINCT keyword with the SUM() function on the "SALARY" column to calculate the sum of unique salary values −

SELECT SUM(DISTINCT SALARY) FROM CUSTOMERS;

Output

The output for the query above is produced as given below −

TotalSalary
33000.00

Print Page