MySQL SUM(): Total Numeric Values in a Column

MySQL SUM() adds the non-NULL numeric values returned by an expression. Use it to calculate totals such as marks, quantities, sales, costs, or profit.

SELECT SUM(mark) AS total_mark
FROM student;

For the sample rows used below, the result is:

SUM() Syntax Top ↑

SELECT SUM(column_name)
FROM table_name;

The expression supplied to SUM() should produce numeric values. MySQL ignores NULL values while calculating the total.

Sample Table and Basic Total Top ↑

The examples on this page use these rows from the student table:

SELECT SUM(mark)
FROM student;

Give the SUM Result an Alias Top ↑

An alias makes the result easier to read and easier to use from application code:

SELECT SUM(mark) AS total_mark
FROM student;

SUM() with WHERE Top ↑

A WHERE clause filters individual rows before SUM() calculates the total.

SELECT SUM(mark) AS total_mark
FROM student
WHERE mark > 55;

Only rows with marks greater than 55 are included in the total.

SUM() with GROUP BY Top ↑

Use GROUP BY when you need one total for each class rather than one total for the complete table.

SELECT class,
       SUM(mark) AS total_mark
FROM student
GROUP BY class
ORDER BY class;

When an aggregate query also selects ordinary columns, those columns normally need to belong to the grouping logic. Do not rely on MySQL returning an arbitrary ungrouped value.

WHERE before GROUP BY Top ↑

You can filter rows first and then calculate totals for each remaining group:

SELECT class,
       SUM(mark) AS total_mark
FROM student
WHERE mark > 55
GROUP BY class
ORDER BY class;

Filter Group Totals with HAVING Top ↑

WHERE filters rows before grouping. HAVING filters the grouped result after the aggregate has been calculated.

SELECT class,
       SUM(mark) AS total_mark
FROM student
GROUP BY class
HAVING SUM(mark) > 200;

With the sample data, only class Four has a total above 200.

SUM() with IN Top ↑

To total marks for selected IDs, combine IN with SUM():

SELECT SUM(mark) AS total_mark
FROM student
WHERE id IN (1, 2, 3, 4);

The result is 275.

SUM(DISTINCT) Top ↑

SUM(DISTINCT expression) adds each distinct non-NULL value only once.

SELECT SUM(DISTINCT mark) AS distinct_mark_total
FROM student;

In the sample data, marks 60 and 55 occur more than once, but each value contributes only once to the distinct total.

How SUM() Handles NULL Top ↑

SUM() ignores NULL values. If no non-NULL value is available for the selected rows, the result is NULL rather than 0.

SELECT SUM(mark) AS total_mark
FROM student
WHERE id > 1000;

If no rows match, the aggregate result is NULL. When the application specifically needs zero instead, use COALESCE():

SELECT COALESCE(
           SUM(mark),
           0
       ) AS total_mark
FROM student
WHERE id > 1000;

See SQL NULL values for the difference between NULL, zero, and an empty string.

SUM() with Expressions and Profit Top ↑

SUM() can aggregate the result of an arithmetic expression. Suppose a product table stores buying and selling prices:

CREATE TABLE plus2_product (
    product VARCHAR(20) NOT NULL,
    buy_price DECIMAL(10,2) NOT NULL,
    sell_price DECIMAL(10,2) NOT NULL
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4;

INSERT INTO plus2_product
    (product, buy_price, sell_price)
VALUES
    ('Product1', 20, 22),
    ('Product2', 15, 20),
    ('Product3', 40, 43),
    ('Product1', 50, 45);

Total profit is the sum of the row-by-row difference between selling price and buying price:

SELECT SUM(sell_price - buy_price) AS profit
FROM plus2_product;

The result is 5.00.

To calculate profit for one product:

SELECT SUM(sell_price - buy_price) AS profit
FROM plus2_product
WHERE product = 'Product1';

Add Several Columns in Each Row Top ↑

If a student table contains separate subject columns, add the columns directly to calculate one student's total:

SELECT id,
       name,
       social + science + math AS total_mark
FROM student_sum;

This adds columns across each row. It is different from:

SELECT SUM(mark)
FROM student;

which adds one expression down multiple rows.

Conditional Totals with CASE Top ↑

A common reporting pattern combines CASE with SUM(). For example, total only marks from class Four:

SELECT SUM(
           CASE
               WHEN class = 'Four' THEN mark
               ELSE 0
           END
       ) AS class_four_total
FROM student;

The result is 250.

Show Detail Rows and a Total Row Top ↑

You can combine detail rows with a final total row using UNION ALL. A sort key makes the final position deterministic:

SELECT product,
       buy_price,
       sell_price
FROM (
    SELECT product,
           buy_price,
           sell_price,
           0 AS sort_order
    FROM plus2_product

    UNION ALL

    SELECT 'Total:',
           SUM(buy_price),
           SUM(sell_price),
           1
    FROM plus2_product
) AS report_rows
ORDER BY sort_order, product;

SUM() without Collapsing Detail Rows Top ↑

GROUP BY returns one row per group. A window function can keep each detail row while adding a group total beside it:

SELECT id,
       name,
       class,
       mark,
       SUM(mark) OVER (
           PARTITION BY class
       ) AS class_total
FROM student
ORDER BY class, id;

This is useful when the report needs both the individual student and the aggregate total. See the existing OVER() and PARTITION BY tutorial.

Common SUM() Mistakes Top ↑

Confusing SUM(column) with adding columns in one row Top ↑

SUM(mark) aggregates values down rows. social + science + math calculates a value across columns in one row.

Selecting unrelated columns without grouping them Top ↑

Do not write aggregate queries that depend on MySQL returning arbitrary values from non-grouped columns. Group by the required dimensions or use a window function when detail rows must remain visible.

Using WHERE to filter an aggregate result Top ↑

Use WHERE to filter rows before aggregation and HAVING to filter totals after GROUP BY.

Assuming SUM() returns zero when nothing matches Top ↑

SUM() can return NULL when there are no non-NULL values to aggregate. Use COALESCE(SUM(...),0) only when zero is the correct application meaning.

Using integer columns for prices by default Top ↑

For exact monetary values, a fixed-point datatype such as DECIMAL(10,2) is normally more appropriate than an old display-width definition such as INT(3).

Using SUM(DISTINCT) to hide duplicate rows Top ↑

DISTINCT changes the calculation. It should represent the business requirement, not compensate for an incorrect JOIN or duplicate-data problem.

Related Tutorials and Downloads Top ↑

Download the existing SQL file for SUM examples

Frequently Asked Questions Top ↑

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: