LearnSQL SUM() & AVG(): Numerical Aggregation & Averages

24 / 61 0%

Lesson 24 of 61

5 mins read

SQL SUM() & AVG(): Numerical Aggregation & Averages

In Plain English

`SUM()` computes the mathematical total sum of numerical values in a column, while `AVG()` computes the arithmetic mean (`SUM(col) / COUNT(col)`). Both functions ignore NULL values during calculation.

The Mental Model

"`SUM()` adds up every deposit on a bank statement to find total incoming cash; `AVG()` divides by the number of transactions to find the average deposit size."

Deep Dive: How It Works

Null Handling in AVG(): If a table has 4 rows with scores (100, 80, NULL, 60), `AVG(score)` evaluates as `(100+80+60) / 3 = 80.0`, NOT dividing by 4.

Handling Missing Values as Zero: If you want NULLs treated as 0 in an average: `AVG(COALESCE(score, 0))`.

Conditional SUM with CASE: `SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END)` calculates refunded revenue inline.

Syntax Blueprint

SELECT
  SUM(sales_amount) AS total_revenue,
  AVG(sales_amount) AS average_sale
FROM transactions;

Use SUM for grand totals and AVG for arithmetic means.

Core Rules to Remember

Numeric Only: SUM and AVG operate on integers, floats, and decimals (fails on strings).

Float Return: AVG returns floating point numbers even when operating on integer columns.

Conditional Sums: Combine SUM with CASE to create pivot tables and analytical dashboards.

Common Beginner Traps & How to Fix Them

Mistake:Forgetting that AVG() ignores NULL rows and assuming it divides by the total row count.

Why it happens: AVG() divides by COUNT(col), not COUNT(*).

How to fix: Use `AVG(COALESCE(col, 0))` if unrecorded rows should be treated as zero.

Pro Tip: Use `SUM(col) * 1.0 / COUNT(*)` to calculate exact averages including NULL rows as zero.

Live Interactive Example

Hit Run Code to see it live

Calculating Total Revenue and Average Ticket Size

Python 3.12

Output Console

Click "Run Code" to view the rendered output.

How it works: SUM calculated $800 total, AVG computed $200 mean, and conditional SUM calculated $650 wholesale.

Your Turn: Micro Challenge

No pressure! Edit the starter code below and test your solution with instant feedback.

Micro Exercise

Calculate Sum and Average

Select `SUM(cost) AS total_cost`, `AVG(cost) AS avg_cost` from `expenses`.

Sandbox Output

Click "Run & Check" to test your solution.

Finished reading and practicing?

Mark this lesson as completed to update your course progress.