How to Get Sum of Multiple Columns in MySQL

In today’s post, we’ll learn how to sum multiple columns in MySQL.

Sum Multiple Columns in MySQL

You can calculate the total values in a set using the aggregate function SUM(). The NULL values are not considered in the calculation by the SUM() function.

The SUM() aggregate function has the following syntax:

SELECT SUM(aggregate_expression)
FROM table_name
WHERE conditions;

The aggregate_expression parameter specifies the column or expression for calculating the total.

The tables from which we wish to get records are specified by table_name. The FROM clause must list one table at a minimum.

The conditions for WHERE are optional. For the records to be recognized, certain requirements must be met, as stated in the WHERE statement.

You must use the CASE statement in a MySQL query to pick several sum columns and show them in distinct columns. When any condition meets the provided statement, the CASE() function in MySQL is used to determine a value by passing over the condition; otherwise, it returns the statement in an else section.

When a condition is met, it halts reading and returns the output. The following is the syntax:

SELECT
    SUM( CASE WHEN column_name1=value_1 THEN column_name2 END ) AS alias_column_1,
    SUM( CASE WHEN column_name1=value_2 THEN column_name2 END ) AS alias_column_2,
FROM yourTableName;

Consider the following example to better understand the previous concept.

-- Total Bonus
SELECT
    SUM(bonus) as TotalBonus
FROM
    EmployeeBonus;
-- Total Marks
SELECT
    SUM(maths+physics+chemistry) as TotalMarks, studentId
FROM
    StudentMarks
GROUP BY
    studentId;

SELECT
    SUM(CASE WHEN subject='Maths' THEN marks END) AS 'Maths TOTAL SCORE',
    SUM(CASE WHEN subject='Physics' THEN marks END) AS 'Physics TOTAL SCORE',
    SUM(CASE WHEN subject='Chemistry' THEN marks END) AS 'Chemistry TOTAL SCORE'
FROM StudentMarks;

In the above example, we are figuring out the total bonus the corporation has paid out so far. In the second operation, we aim to obtain the cumulative grades of every student across all topics.

Run the above code line in any browser compatible with MySQL. It will display the following outcome:

+------------+
| TotalBonus |
+------------+
| $123445663 |
+------------+
1 row in set (0.00 sec)

+------------+-----------+
| TotalMarks | studentId |
+------------+-----------+
| 278        |       1   |
| 256        |       2   |
| 289        |       3   |
+------------+-----------+
3 row in set (0.01 sec)