Sum of Values for Multiple Columns in SQL

June 6, 2023 | by Arround The Web | No comments

Sum of Values for Multiple Columns in SQL

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:

SUM(column_name)

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:

select sum(product_price) as total from products p;

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.

create table students (
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:

SELECT name, science_score + math_score + history_score + other AS total_score
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:

SELECT SUM(column1 + column2 + column3) AS total_sum FROM table_name;

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:

SELECT name, science_score + math_score + history_score + other AS total_score
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.

Source: linuxhint.com