JIYIK CN >

In this article, we will learn how to sum multiple columns in MySQL.

Sum multiple columns in MySQL

You can use aggregate functions SUM()to calculate the total value in a collection. SUM()The function calculation does not consider NULL values.

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 used to calculate the aggregate.

The table from which we wish to obtain records is specified by table_name. The FROM clause must list at least one table.

The WHERE condition is optional. To identify a record, certain requirements must be met, as described in the WHERE clause.

You must use CASE statement in MySQL query to select multiple sum columns and display them in different columns. The CASE() function in MySQL determines a value by passing the condition when any condition satisfies the provided statement; otherwise, it returns the statement in the else part.

When the condition is met, it stops reading and returns the output. 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 preceding concepts.

-- 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 calculating the total bonus paid by the company so far. In the second operation, our goal is to get the cumulative grade of each student across all subjects.

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

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

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