SQL SUM Function – Syntax and Examples

SQL SUM Function

The SQL SUM function is used to calculate the total sum of a numeric column. It adds up all the values in the specified column and returns the total.

The SUM function is often used with the GROUP BY clause to get the total for each group within the dataset.

In this tutorial, we will go through SQL SUM Function, its syntax, and how to use this function in SQL statements, with the help of well detailed examples.

---

Syntax of SQL SUM Function

The basic syntax of the SQL SUM function is as follows:

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

Each part of this syntax has a specific purpose:

• SELECT: Specifies the column to retrieve the total sum from.
• SUM(column_name): The SUM function, which calculates the total sum of non-NULL values in the specified column.
• AS total_value: An alias for the calculated sum in the results.
• FROM: Specifies the table from which to retrieve data.
• WHERE (optional): Filters the data based on specified conditions before applying the SUM function.

---

Step-by-Step Examples with MySQL

We’ll go through various examples demonstrating the SUM function in MySQL. Using MySQL 8.0 with MySQL Workbench, we’ll use a sample students table with fields id, name, age, grade, and locality along with a grades table that records students’ scores.

Setup for Examples: Creating the Database and Tables

1. Open MySQL Workbench and create a new database:

CREATE DATABASE school;

2. Select the school database:

USE school;

3. Create a students table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50),
    age INT,
    grade VARCHAR(10),
    locality VARCHAR(50)
);

4. Create a grades table:

CREATE TABLE grades (
    student_id INT,
    subject VARCHAR(50),
    score INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

5. Insert sample data into the students and grades tables:

INSERT INTO students (name, age, grade, locality)
VALUES
('Alice', 14, '8th', 'Northside'),
('Bob', 15, '9th', 'Westend'),
('Charlie', 14, '8th', 'Northside'),
('David', 16, '10th', 'Southend'),
('Eva', 15, '9th', 'Westend');

INSERT INTO grades (student_id, subject, score)
VALUES
(1, 'Math', 85),
(2, 'Math', 78),
(3, 'Science', 90),
(4, 'Math', 88),
(5, 'Science', 70);