MySQL SUM() Function

Introduction

In this chapter, we will learn about the SUM() function in MySQL. The SUM() function is an aggregate function that returns the total sum of a numeric column. It is widely used to calculate totals, subtotals, and other aggregate values in a dataset. We will cover the syntax for the SUM() function, examples of its usage, and important considerations for using it in MySQL.

Syntax

The basic syntax for using the SUM() function in MySQL is:

SELECT SUM(column_name)
FROM table_name
[WHERE condition];

Example: Calculate the Total Salary

SELECT SUM(salary) AS total_salary
FROM employees;

Full Example

Let’s go through a complete example where we create a database and table, insert data, and demonstrate the usage of the SUM() function.

1. Create a Database and Table

CREATE DATABASE company;
USE company;

CREATE TABLE employees (
    employee_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

INSERT INTO employees (first_name, last_name, department, salary) VALUES
('Rahul', 'Sharma', 'Sales', 50000.00),
('Priya', 'Singh', 'Marketing', 60000.00),
('Amit', 'Kumar', 'Sales', 55000.00),
('Neha', 'Verma', 'IT', 58000.00),
('Sahil', 'Mehta', 'IT', 62000.00),
('Kiran', 'Patel', 'Sales', 53000.00);

2. Using SUM()

Example: Calculate the Total Salary

SELECT SUM(salary) AS total_salary
FROM employees;

Output

Example: Calculate the Total Salary by Department

You can use the SUM() function with the GROUP BY clause to calculate the total salary for each department.

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Output

Example: Calculate the Total Salary for a Specific Department

You can use the SUM() function with the WHERE clause to calculate the total salary for employees in a specific department.

SELECT SUM(salary) AS sales_total_salary
FROM employees
WHERE department = 'Sales';

Output

Using SUM() with Other Aggregate Functions

The SUM() function can be used in combination with other aggregate functions for more complex queries.

Example: Calculate the Average and Total Salary by Department

SELECT department,
       AVG(salary) AS average_salary,
       SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Output

Important Considerations

• NULL Values: The SUM() function ignores NULL values. Ensure that the column being summed does not have NULL values if this is not the desired behavior.
• Data Types: The SUM() function works with numeric data types. Ensure that the column being summed contains numeric values.
• Performance: The SUM() function can impact performance, especially on large datasets. Consider indexing columns used in the WHERE clause to improve performance.

Conclusion

The SUM() function in MySQL is used for calculating the total sum of a numeric column. This chapter covered the syntax for using the SUM() function, provided examples of its usage, and discussed important considerations.