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);

---

Examples: Using SUM Function in Queries

Now, let’s explore different scenarios of using the SUM function with these tables.

---

1. Calculating the Total Score Across All Subjects

To find the total score in the grades table:

SELECT SUM(score) AS total_score
FROM grades;

This query returns the sum of all scores across subjects in the grades table.

---

2. Calculating the Total Age of Students

To find the total age of all students in the students table:

SELECT SUM(age) AS total_age
FROM students;

This query returns the total of all ages in the students table.

---

3. Using SUM with a WHERE Clause

To find the total score for the subject “Math” only:

SELECT SUM(score) AS total_math_score
FROM grades
WHERE subject = 'Math';

This query calculates the total score for “Math” in the grades table, filtering scores only for that subject.

Reference: SQL WHERE Clause

---

4. Using SUM with GROUP BY to Find Total Scores per Subject

To find the total score for each subject:

SELECT subject, SUM(score) AS total_score
FROM grades
GROUP BY subject;

This query returns the total score for each subject in the grades table, displaying the total score per subject.

Reference: SQL GROUP BY

---

FAQs for SQL SUM

1. What does the SQL SUM function do?

The SUM function calculates the total of all non-NULL values in a specified column.

2. Can SUM be used with non-numeric data?

No, SUM is designed for numeric data. It does not work with text or date data types.

3. How do I find the total value for each group?

Use the GROUP BY clause with SUM to find the total value for each group in the dataset.

4. Can SUM be used with WHERE conditions?

Yes, you can filter rows before calculating the total by using a WHERE clause with the SUM function.

5. Does SUM consider NULL values?

No, SUM ignores NULL values in the column and only calculates the total of non-NULL values.