In SQL, the SUM() function is an aggregate function that allows you to calculate the sum of a set of values in a specified table column. We mainly use this function to calculate the sum of numerical values in a given column or a table expression.
This can be useful in a wide range of scenarios such as calculating a company’s total revenue, the total sales of a product, or the total number of hours that the employees work in a month.
However, in this tutorial, we will learn how we can use the sum() function in SQL to calculate the sum of values for multiple columns in a single statement.
SQL Sum Function
The function syntax is as expressed in the following:
The function takes the column name that you wish to sum up as the argument. You can also use the expressions in the SUM() function to sum up the calculated values.
Suppose we have a table that contains the product information as follows:
We can use the sum function to calculate the total price for all the products as shown in the following example query:
The query should return a sum of all the values in the table.
Sum of Multiple Columns in SQL
Suppose we have a table that contains the student information and the score for each student in varios subjects.
id int auto_increment not null primary key,
name varchar(50),
science_score int not null,
math_score int not null,
history_score int not null,
other int not null
);
INSERT INTO students (name, science_score, math_score, history_score, other)
VALUES
('John Doe', 80, 70, 90, 85),
('Jane Smith', 95, 85, 80, 92),
('Tom Wilson', 70, 75, 85, 80),
('Sara Lee', 88, 92, 90, 85),
('Mike Johnson', 75, 80, 72, 68),
('Emily Chen', 92, 88, 90, 95),
('Chris Brown', 85, 80, 90, 88),
('Lisa Kim', 90, 85, 87, 92),
('Mark Davis', 72, 68, 75, 80),
('Ava Lee', 90, 95, 92, 88);
The resulting table is as follows:
We can use the sum() function to calculate the total score for each student’s subjects as demonstrated in the following:
FROM students;
The previous query shows us how to sum the multiple tables in a single query using the sum() function in SQL.
The syntax is as expressed in the following:
Once you calculate the sum value, you can incorporate the other SQL features such as sorting from highest to lowest as shown in the following:
FROM students order by total_score desc;
Resulting Output:
Conclusion
You came across the sum() function. This function allows us to calculate the sum of numerical values for single or multiple columns in a table or table expression.
