SQL SUM Function

SQL SUM Function

The 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.

---

Syntax

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.

---

Example SQL SUM Queries

Let's look at some examples of SQL SUM queries using the employees table:

1. Basic SUM Example

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.

2. SUM with WHERE Clause

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.

3. SUM with GROUP BY

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.

4. SUM with JOIN

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.

---

Full Example

Let's go through a complete example that includes creating a database, creating tables, inserting data, and using the SUM function.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating Tables

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.

Step 3: Inserting Data into the Tables

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.

Step 4: Using the SUM Function

This step involves using the SUM function to perform various aggregate operations on the employees table.

Example 1: Calculating Total Salary

SELECT SUM(salary) AS total_salary
FROM employees;

This query calculates the total salary of all employees in the employees table.

Example 2: Calculating Total Salary for a Specific Department

SELECT SUM(salary) AS total_salary
FROM employees
WHERE department_id = 3;

This query calculates the total salary of employees in department 3.

Example 3: Calculating Total Salary by Department

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.

---

Conclusion

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.