MySQL SUM AVG: Totals and Averages with NULL

SUM adds values. AVG averages them.

query.sql

SQL

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    city VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    ordered_on DATE NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) NOT NULL
);

INSERT INTO customers (name, city) VALUES
    ('Maya Shah',    'Pune'),
    ('Rohan Verma',  'Delhi'),
    ('Sana Ali',     'Pune'),
    ('Tara Nair',    'Kochi'),
    ('Uday Singh',   'Delhi');

INSERT INTO orders (customer_id, ordered_on, total, status) VALUES
    (1, '2026-05-02',  680.00, 'delivered'),
    (1, '2026-05-20', 1299.00, 'delivered'),
    (2, '2026-05-21',  145.00, 'shipped'),
    (3, '2026-06-01',  450.00, 'delivered'),
    (3, '2026-06-05',   80.00, 'cancelled'),
    (3, '2026-06-18',  999.00, 'shipped'),
    (2, '2026-06-20',  220.00, 'pending');

query.sql

SQL

SELECT SUM(total)             AS revenue,
       AVG(total)             AS average_order,
       ROUND(AVG(total), 2)   AS average_rounded,
       COUNT(*)               AS orders
FROM orders;

Output

OUTPUT

+---------+---------------+-----------------+--------+
| revenue | average_order | average_rounded | orders |
+---------+---------------+-----------------+--------+
| 3873.00 |    553.285714 |          553.29 |      7 |
+---------+---------------+-----------------+--------+
1 row in set

Only some rows

query.sql

SQL

SELECT SUM(total) AS delivered_revenue FROM orders WHERE status = 'delivered';
SELECT SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'cancelled' THEN total ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status IN ('shipped', 'pending') THEN total ELSE 0 END) AS in_progress
FROM orders;

Output

OUTPUT

+-------------------+
| delivered_revenue |
+-------------------+
|           2429.00 |
+-------------------+
1 row in set

+-----------+-----------+-------------+
| delivered | cancelled | in_progress |
+-----------+-----------+-------------+
|   2429.00 |     80.00 |     1364.00 |
+-----------+-----------+-------------+
1 row in set

One pass over the table gives several different totals. This conditional aggregation replaces several queries.

Per customer

query.sql

SQL

SELECT customer_id, COUNT(*) AS orders, SUM(total) AS spent, ROUND(AVG(total), 2) AS average_order
FROM orders
WHERE status <> 'cancelled'
GROUP BY customer_id
ORDER BY spent DESC;

Output

OUTPUT

+-------------+--------+---------+---------------+
| customer_id | orders | spent   | average_order |
+-------------+--------+---------+---------------+
|           1 |      2 | 1979.00 |        989.50 |
|           3 |      2 | 1449.00 |        724.50 |
|           2 |      2 |  365.00 |        182.50 |
+-------------+--------+---------+---------------+
3 rows in set

Average of what?

AVG(total) averages the order totals. Be careful with averages of averages: the average of two group averages is not the overall average unless the groups have the same size.

query.sql

SQL

1
2
3

SELECT status, ROUND(AVG(total), 2) AS group_average FROM orders GROUP BY status ORDER BY status;
SELECT ROUND(AVG(avg_total), 2) AS average_of_averages, ROUND((SELECT AVG(total) FROM orders), 2) AS true_average
FROM (SELECT AVG(total) AS avg_total FROM orders GROUP BY status) AS g;

Output

OUTPUT

+-----------+---------------+
| status    | group_average |
+-----------+---------------+
| cancelled |         80.00 |
| delivered |        809.67 |
| pending   |        220.00 |
| shipped   |        572.00 |
+-----------+---------------+
4 rows in set

+---------------------+--------------+
| average_of_averages | true_average |
+---------------------+--------------+
|              420.42 |       553.29 |
+---------------------+--------------+
1 row in set

The two numbers differ, because each status has a different number of orders. Calculate from the raw rows, or use a weighted average: SUM(x * weight) / SUM(weight).

NULL and empty sets

SUM and AVG skip NULL, and return NULL when there is nothing to add:

query.sql

SQL

SELECT SUM(total) AS sum_of_nothing, AVG(total) AS average_of_nothing, COALESCE(SUM(total), 0) AS sum_or_zero FROM orders WHERE status = 'refunded';

Output

OUTPUT

+----------------+--------------------+-------------+
| sum_of_nothing | average_of_nothing | sum_or_zero |
+----------------+--------------------+-------------+
|           NULL |               NULL |        0.00 |
+----------------+--------------------+-------------+
1 row in set

Result types

Input type	`SUM` returns	`AVG` returns
Integer	`DECIMAL`	`DECIMAL`
`DECIMAL`	`DECIMAL`	`DECIMAL` (a few more decimal places)
`FLOAT` / `DOUBLE`	`DOUBLE`	`DOUBLE`

So an average of whole numbers usually shows decimals (2.5000). Round it for display. Keep prices in DECIMAL, so sums stay exact (DECIMAL vs FLOAT).

Percent of total

query.sql

SQL

SELECT status, SUM(total) AS amount, ROUND(100 * SUM(total) / (SELECT SUM(total) FROM orders), 1) AS percent_of_revenue
FROM orders
GROUP BY status
ORDER BY amount DESC;

Output

OUTPUT

+-----------+---------+--------------------+
| status    | amount  | percent_of_revenue |
+-----------+---------+--------------------+
| delivered | 2429.00 |               62.7 |
| shipped   | 1144.00 |               29.5 |
| pending   |  220.00 |                5.7 |
| cancelled |   80.00 |                2.1 |
+-----------+---------+--------------------+
4 rows in set

A window function does the same without a subquery: SUM(SUM(total)) OVER () (Window Aggregates).

SUM(DISTINCT)

SUM(DISTINCT x) adds each different value once. It is rarely right. If two orders have the same total, only one is counted. Use it only when you really mean “sum of the distinct values”.

lightbulb

Round for display, not for math

Calculate with full precision and round the final value: ROUND(SUM(x), 2). Rounding each row before summing makes cent differences add up.