Python SQL SUM() & AVG(): Numerical Aggregation & Averages
`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.
"`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
Common Beginner Traps & How to Fix Them
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.
Live Interactive Example
Hit Run Code to see it liveYour Turn: Micro Challenge
No pressure! Edit the starter code below and test your solution with instant feedback.
Calculate Sum and Average
- Select `SUM(cost) AS total_cost`, `AVG(cost) AS avg_cost` from `expenses`.
Finished reading and practicing?
Mark this lesson as completed to update your course progress.