SUM & AVG

SUM and AVG are the two aggregate functions you use to total and average numeric data. Both are straightforward on clean data, but both have the same easy-to-miss behavior around NULL values that can throw off your numbers if you are not paying attention.

SUM — totaling a column

SQL

SELECT SUM(total) AS total_revenue
FROM orders
WHERE status = 'delivered';

SUM(column) adds up every non-NULL value in that column. Rows where the column is NULL are simply skipped — they are not treated as zero, they are excluded from the addition entirely.

AVG — computing the mean

SQL

SELECT AVG(total) AS average_order_value
FROM orders
WHERE status = 'delivered';

AVG divides by the count of non-NULL values, not the row count

This is the single most important thing to know about AVG: it computes SUM(column) / COUNT(column), where both the sum and the count only consider non-NULL rows. It does not divide by the total number of rows in the group. If some rows have a NULL in the column you are averaging, they are excluded from both the numerator and the denominator — which can noticeably shift the result compared to what you might expect.

Worked example

feedback_scores

SQL

-- feedback_scores table
-- id | customer_id | score
-- ---+-------------+-------
--  1 |           1 |    10
--  2 |           2 |     8
--  3 |           3 |  NULL   (customer skipped the rating)
--  4 |           4 |     6
--  5 |           5 |  NULL   (customer skipped the rating)

SELECT
  SUM(score)            AS total_score,
  COUNT(*)              AS total_rows,
  COUNT(score)           AS rated_rows,
  AVG(score)             AS average_score
FROM feedback_scores;

total_score | total_rows | rated_rows | average_score
------------+------------+------------+---------------
         24 |          5 |          3 |              8

Notice that AVG(score) is 8, computed as 24 / 3 — the sum divided by the number of rows that actually had a score. It is not 24 / 5 = 4.8, even though there are 5 rows in the table. If you wanted the average across all rows, treating skipped ratings as zero, you would need to convert the NULLs first:

SQL

-- Treat NULL scores as 0 before averaging, if that is really what you want
SELECT AVG(COALESCE(score, 0)) AS average_including_skips
FROM feedback_scores;
-- (10 + 8 + 0 + 6 + 0) / 5 = 4.8

SUM(column) adds up non-NULL values; NULLs are skipped, not treated as zero.
AVG(column) divides the sum by the count of non-NULL values, not the total row count.
Mixing NULLs into a column you are averaging can noticeably shift the result.
Use COALESCE(column, 0) if you specifically want NULLs treated as zero.