SUM and AVG calculations

Concepts covered: Sums & Averages

SUM adds every non-null value of a column within a group and AVG divides that sum by the count of non-null values. Both take a numeric column or a numeric expression, both return 1 value per group, and both treat NULL the same way: the row is skipped as if it were absent. That shared rule is what makes AVG behave in ways that surprise people, which is why nulls get their own treatment below.

A total per region

The 6 transactions from the first section carry amounts: North has 150, 175 and 190, South has 200 and 225, East has 160. A total per region needs the same GROUP BY region as the count did, with SUM(amount) in place of COUNT(*). Watch each amount leave its row and land in its region's running total.

SELECT region, SUM(amount) AS total_revenue
FROM transactions
GROUP BY region

North totals 515, South 425 and East 160, and 515 plus 425 plus 160 is 1,100, the grand total of the table. Dropping GROUP BY from the query returns that 1,100 as a single row, because with no grouping column the whole table is 1 group. A SUM that reads the wrong column is a common slip: SUM(quantity) is units moved and SUM(amount) is money, and the 2 can differ by a factor of 100 while both looking like plausible revenue.

AVG is SUM divided by COUNT

AVG(amount) for North is 515 divided by 3, which is 171.67 after rounding. South is 425 divided by 2, which is 212.5, and East is 160 divided by 1. AVG carries out that division itself, so SUM(amount) / COUNT(amount) and AVG(amount) return the same value, with 1 caveat about integer columns below. The employee_metrics table in the next animation groups salaries by department: Engineering holds 120,000, 130,000 and 125,000, averaging 125,000; Sales holds 80,000 and 90,000, averaging 85,000; Marketing has 1 salary of 95,000, and the average of 1 value is that value.

An average of integers can lose its fraction. On an integer column, PostgreSQL's AVG returns a numeric with decimals, Trino's returns a double and MySQL's a decimal. The hand-written division SUM(amount) / COUNT(*) is still integer division on PostgreSQL and Trino, and it truncates 515 / 3 to 171. SQL Server truncates in AVG itself: its AVG over an int column returns an int, so AVG(amount) for North is 171 there too. Casting the argument gives 171.67 on every engine, written as AVG(amount * 1.0) or AVG(CAST(amount AS decimal(10,2))). For the hand-written version, SUM(amount) * 1.0 / COUNT(*) does the same.

What NULL does to SUM and AVG

The bonuses table below has 3 employees. Two received a bonus and 1 has NULL, meaning no bonus was recorded. The functions read the 3 rows differently from how a person would.

SUM(bonus) is 1,500, and AVG(bonus) is 750, because the average divides by COUNT(bonus), which is 2, and Cal does not take part. If the business meaning of a missing bonus is zero, the correct average is 1,500 divided by 3, which is 500, and the query has to say so: AVG(COALESCE(bonus, 0)) replaces each NULL with 0 before averaging. The 2 answers differ by 50 percent on 3 rows, so a report built on the default is reporting the average bonus among people who received one, which may or may not be the question asked. A group where every value is NULL gives SUM and AVG of NULL, never 0.

Summing an expression

The argument to SUM can be arithmetic over several columns, computed per row before the adding starts. An order_items table storing quantity and unit_price has no revenue column, and SUM(quantity * unit_price) builds it: each row's quantity times its price, then the products added within the group. SUM(quantity) * SUM(unit_price) is a different and wrong number, since it multiplies 2 totals that were never meant to meet. The practice orders table has a profit per order and a region; fill in the 2 aggregates that give total and average profit per region.

SUM(profit) and AVG(profit) in the same row let a reader spot the region whose high total comes from many small orders rather than a few large ones, the same volume-versus-size reading the 2 counts gave in the previous section.

About this lesson

This section is part of the Aggregating: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.