Aggregate Functions

What are aggregate functions?

Aggregate functions take a set of rows and collapse them into a single value. You already know COUNT — the others work the same way. They scan the rows you specify and return one number: a total, an average, a minimum, or a maximum.

SELECT SUM(price_usd)   AS total_revenue,
       AVG(price_usd)   AS avg_price,
       MIN(price_usd)   AS cheapest,
       MAX(price_usd)   AS most_expensive
FROM coffee_shop_sales;

All four functions can be used in the same query. Each one independently scans the same set of rows and returns a single result.

SUM — total of all values

SUM adds up every value in a numeric column. Use it for totals: total revenue, total quantity sold, total distance travelled.

-- Total revenue across all orders
SELECT SUM(order_total_usd) AS total_revenue
FROM food_delivery_orders;

-- Total items sold across all transactions
SELECT SUM(quantity) AS total_items_sold
FROM grocery_store_sales;

AVG — mean of all values

AVG divides the sum of a column by the number of non-NULL rows. Use it when you need a typical or representative value.

-- Average workout duration across all visits
SELECT AVG(workout_duration_minutes) AS avg_duration
FROM gym_member_visits;

-- Average nightly rate across all hotel bookings
SELECT AVG(price_per_night) AS avg_nightly_rate
FROM hotel_room_bookings;

NULL values are ignored by AVG — they are not counted as zero, they are simply excluded from both the sum and the row count.

MIN and MAX — extremes

MIN returns the smallest value in a column; MAX returns the largest. Both work on numbers, text (alphabetical order), and dates (chronological order).

-- Cheapest and most expensive rental rate
SELECT MIN(daily_rate_usd) AS cheapest_rate,
       MAX(daily_rate_usd) AS most_expensive_rate
FROM car_rental_records;

-- Earliest and latest order dates
SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS latest_order
FROM online_bookstore_orders;

Combining with WHERE

WHERE filters rows before the aggregate function runs. This lets you calculate a total, average, or extreme for a specific subset of data.

-- Average price of only Fantasy book orders
SELECT AVG(price_usd) AS avg_fantasy_price
FROM online_bookstore_orders
WHERE book_genre = 'Fantasy';

-- Total revenue from orders over $30
SELECT SUM(order_total_usd) AS high_value_revenue
FROM food_delivery_orders
WHERE order_total_usd > 30;

Combining multiple aggregates

You can use several aggregate functions in one SELECT — each one runs independently over the same set of rows. This is efficient because the database only scans the table once.

-- Full summary in one query
SELECT COUNT(*)                  AS total_rentals,
       AVG(daily_rate_usd)       AS avg_rate,
       MIN(daily_rate_usd)       AS cheapest,
       MAX(daily_rate_usd)       AS most_expensive,
       SUM(daily_rate_usd)       AS total_rate_value
FROM car_rental_records;

Common mistakes

Using SUM or AVG on a text column — these functions only make sense on numeric columns. Applying them to text will either error or return unexpected results depending on the database.

Expecting AVG to include NULLs as zero — AVG skips NULL values entirely. If a column has 10 rows but 3 are NULL, AVG divides by 7, not 10. This is usually what you want, but worth being aware of.

Mixing aggregated and non-aggregated columns without GROUP BY — writing SELECT drink_type, SUM(price_usd) without a GROUP BY will error in most databases. Either aggregate every non-grouped column or add a GROUP BY clause.