ProgramGuruThe SQL SUM function is used to calculate the total sum of a numeric column. This function is essential for performing various aggregate operations, such as summing up sales, salaries, or any other numeric data.
SELECT SUM(column_name)
FROM table_name
WHERE condition;
The SUM function returns the total sum of the specified column. The WHERE clause is optional and can be used to filter the rows before applying the SUM function.
Let's look at some examples of SQL SUM queries using the employees table:
SELECT SUM(salary) AS total_salary
FROM employees;
This query uses the SUM function to calculate the total salary of all employees in the employees table.
SELECT SUM(salary) AS total_salary
FROM employees
WHERE department_id = 3;
This query uses the SUM function to calculate the total salary of employees in department 3.
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
This query uses the SUM function to calculate the total salary for each department in the employees table.
SELECT d.department_name, SUM(e.salary) AS total_salary
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;
This query uses the SUM function to calculate the total salary for each department by joining the employees and departments tables.
Let's go through a complete example that includes creating a database, creating tables, inserting data, and using the SUM function.
This step involves creating a new database named example_db.
CREATE DATABASE example_db;
In this example, we create a database named example_db.
In this step, we create two tables named employees and departments within the previously created database.
USE example_db;
CREATE TABLE departments (
department_id INT AUTO_INCREMENT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL
);
CREATE TABLE employees (
employee_id INT AUTO_INCREMENT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
department_id INT,
salary DECIMAL(10, 2),
FOREIGN KEY (department_id) REFERENCES departments(department_id)
);
Here, we define the departments table with columns for department_id and department_name. We also define the employees table with columns for employee_id, first_name, last_name, department_id, and salary, with a foreign key relationship to the departments table.
This step involves inserting some sample data into the departments and employees tables.
INSERT INTO departments (department_name) VALUES ('HR'), ('Finance'), ('IT'), ('Marketing');
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('John', 'Doe', 1, 50000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Jane', 'Smith', 2, 60000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Alice', 'Johnson', 3, 70000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Bob', 'Brown', 4, 80000.00);
INSERT INTO employees (first_name, last_name, department_id, salary) VALUES ('Charlie', 'Davis', 1, 90000.00);
Here, we insert data into the departments table and then insert data into the employees table, ensuring that each employee is assigned to a department.
This step involves using the SUM function to perform various aggregate operations on the employees table.
SELECT SUM(salary) AS total_salary
FROM employees;
This query calculates the total salary of all employees in the employees table.
SELECT SUM(salary) AS total_salary
FROM employees
WHERE department_id = 3;
This query calculates the total salary of employees in department 3.
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;
This query calculates the total salary for each department in the employees table.
The SQL SUM function is a powerful tool for calculating the total sum of a numeric column. Understanding how to use the SUM function and its syntax is essential for performing various aggregate operations and data analysis in a relational database.