MySQL - SUM() Function

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
Advertisements