Chapter 26: PostgreSQL AVG Function
PostgreSQL: the AVG() function ☕📊
You’ve already learned SELECT, GROUP BY, operators, and built small demo tables. Today we focus on how to calculate averages properly — because AVG() looks simple, but people make very common mistakes with it (especially with grouping, data types, NULLs, and rounding).
1. What does AVG() actually do? (Honest teacher explanation)
AVG() is an aggregate function that computes the arithmetic mean (average) of a set of numeric values.
Formula inside PostgreSQL:
|
0 1 2 3 4 5 6 |
AVG = (sum of all non-NULL values) / (count of non-NULL values) |
Important points right now in PostgreSQL 18 (2026 reality):
- Ignores NULL values completely (does not count them in the denominator)
- Input types: works on smallint, integer, bigint, real, double precision, numeric, interval
- Return type:
- numeric when input is integer types
- double precision when input is floating-point (real or double precision)
- same as input for numeric or interval
- Very precise with numeric type (no floating-point rounding errors)
- Can be used in SELECT, HAVING, subqueries, window functions, etc.
Official short description (from current PostgreSQL docs):
avg(expression) — the average (arithmetic mean) of all input values
2. Basic syntax patterns (what you’ll type 90% of the time)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
-- Simple average of whole table/column SELECT AVG(column_name) FROM table_name; -- With alias & rounding (very common) SELECT AVG(column_name)::NUMERIC(10,2) AS average_value FROM table_name; -- With WHERE filter SELECT AVG(salary) FROM employees WHERE department = 'Sales'; -- With GROUP BY (the real power) SELECT category, AVG(price) FROM products GROUP BY category; |
3. Real examples – using our students table (from earlier classes)
Assume this students table (some rows for illustration):
| id | first_name | gpa | enrollment_year | fees_paid |
|---|---|---|---|---|
| 1 | Aarav | 8.95 | 2025 | TRUE |
| 2 | Priya | 9.60 | 2024 | FALSE |
| 3 | Rahul | 7.85 | 2025 | FALSE |
| 4 | Sneha | 9.20 | 2025 | TRUE |
| 5 | Meera | 10.00 | 2025 | TRUE |
| 6 | Kiran | NULL | 2023 | FALSE |
Example 1: Overall class average GPA
|
0 1 2 3 4 5 6 7 |
SELECT AVG(gpa) AS overall_average_gpa FROM students; |
Result (approx):
|
0 1 2 3 4 5 6 7 8 |
overall_average_gpa ──────────────────── 9.1200000000000000 |
→ Notice: Kiran’s NULL GPA is ignored — denominator = 5, not 6
Example 2: Nice rounded display (most common real usage)
|
0 1 2 3 4 5 6 7 8 9 |
SELECT ROUND(AVG(gpa), 2) AS avg_gpa_rounded, AVG(gpa)::NUMERIC(4,2) AS avg_gpa_numeric FROM students; |
Result:
|
0 1 2 3 4 5 6 7 8 |
avg_gpa_rounded | avg_gpa_numeric ────────────────┼───────────────── 9.12 | 9.12 |
→ ROUND() or ::NUMERIC(precision,scale) — choose whichever you prefer
Example 3: Average GPA per enrollment year (GROUP BY)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT enrollment_year, COUNT(*) AS student_count, AVG(gpa) AS avg_gpa, ROUND(AVG(gpa), 2) AS rounded_avg FROM students GROUP BY enrollment_year ORDER BY enrollment_year; |
Result (example):
|
0 1 2 3 4 5 6 7 8 9 10 |
enrollment_year | student_count | avg_gpa | rounded_avg ────────────────┼───────────────┼──────────────────┼───────────── 2023 | 1 | | 2024 | 1 | 9.6000000000000000 | 9.60 2025 | 4 | 8.9000000000000000 | 8.90 |
→ 2023 has NULL GPA → AVG(gpa) returns NULL for that group
Example 4: Average only for active students + HAVING filter
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT enrollment_year, AVG(gpa)::NUMERIC(4,2) AS avg_gpa FROM students WHERE is_active = TRUE GROUP BY enrollment_year HAVING AVG(gpa) >= 9.0 ORDER BY avg_gpa DESC; |
→ Only years where average GPA is 9.0 or higher appear
Example 5: Average with JOIN (realistic bookstore style)
Imagine our order_items table:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
SELECT b.title, AVG(oi.unit_price) AS avg_selling_price, COUNT(oi.id) AS times_sold FROM books b JOIN order_items oi ON oi.book_id = b.id GROUP BY b.title HAVING COUNT(oi.id) >= 2 ORDER BY avg_selling_price DESC; |
→ Shows average price per book title (useful if prices change over time)
4. Common traps & best practices (from real student & production experience)
| Mistake / Question | What happens / Wrong result | Correct way / Explanation |
|---|---|---|
| AVG(column) on table with many NULLs | Average looks higher than expected | NULLs are ignored — correct behavior |
| Want to treat NULL as 0 | — | AVG(COALESCE(gpa, 0)) |
| Floating point weirdness | 3.3333333333333335 instead of nice number | Cast: AVG(…)::NUMERIC(10,2) or ROUND(AVG(…), 2) |
| GROUP BY without aggregate on other cols | ERROR: column must appear in GROUP BY or aggregate | Add to GROUP BY or wrap in aggregate |
| Average of intervals | Works! (avg duration) | AVG(end_time – start_time) → interval result |
| Very large dataset performance | Slow without index on GROUP BY columns | Index the grouping columns |
5. Quick summary cheat-sheet (keep this in your notes)
- AVG() → arithmetic mean, ignores NULLs
- Return type → numeric or double precision mostly
- Best display: ROUND(AVG(col), 2) or ::NUMERIC(p,s)
- With GROUP BY → one average per group
- With HAVING → filter groups after averaging
- With COALESCE() → treat NULL as 0 if needed
- Works great with JOIN, subqueries, CTEs, window functions
Your mini practice right now (try in your database)
- Calculate overall average GPA
- Average GPA per enrollment year (with rounded display)
- Average GPA only for students with GPA IS NOT NULL and fees_paid = TRUE
- Add a dummy salary column to students → calculate average salary per city (if you have city column)
Next class?
Tell me:
- Want SUM(), COUNT(), MIN/MAX in detail (other aggregates)?
- AVG() inside window functions (running average, moving average)?
- How AVG() behaves with money / interval / weighted average?
- Or deeper into GROUP BY + HAVING + WHERE combinations?
Your teacher is right here — what’s next? 🚀
