Aggregate Functions — COUNT, SUM, AVG, MIN, MAX

Aggregate functions reduce a set of rows to a single summary value. They're essential for analytics and reporting.

The Five Standard Aggregates

NULL Handling Is Critical

The distinction between COUNT(*) and COUNT(col) trips up many developers:

-- Table: scores (id, value)
-- Rows: (1, 100), (2, NULL), (3, 50)

SELECT COUNT(*) FROM scores        → 3  (counts all rows)
SELECT COUNT(value) FROM scores    → 2  (skips NULL)
SELECT SUM(value) FROM scores      → 150 (100 + 50, NULL skipped)
SELECT AVG(value) FROM scores      → 75.00 (150/2, not 150/3!)

AVG only divides by the count of non-NULL values. This is mathematically correct for "average of known values" but can surprise people who expect division by total row count.

Implementation

Aggregates process rows one at a time in a single pass:

AVG Precision

AVG returns a floating-point result, formatted to 2 decimal places:

SELECT AVG(age) FROM users
→ 28.33

Multiple Aggregates in One Query

SELECT COUNT(*), SUM(age), AVG(age), MIN(age), MAX(age) FROM users
→ COUNT(*)|SUM(age)|AVG(age)|MIN(age)|MAX(age)
→ 3|85|28.33|25|30

All aggregates are computed in a single pass through the data.

Index Optimization for MIN/MAX

MIN and MAX can be optimized with indexes:

CREATE INDEX idx_age ON users(age)
SELECT MIN(age) FROM users   → read first leaf of index B-tree
SELECT MAX(age) FROM users   → read last leaf of index B-tree

No table scan needed — just one B-tree lookup. PostgreSQL does this automatically.

How PostgreSQL Computes Aggregates

PostgreSQL's aggregate framework (nodeAgg.c) supports transition functions, combine functions (for parallel aggregation), and final functions. This lets PostgreSQL parallelize aggregates across multiple workers, each computing partial aggregates that are combined at the end.

Your Task

Implement COUNT(*), COUNT(col), SUM, AVG, MIN, and MAX with proper NULL handling. AVG should output 2 decimal places.