SQL SUM, AVG, MIN, MAX Functions (Complete Guide)

Overview

SUM adds values, AVG calculates the mean, MIN finds the smallest, MAX finds the largest. These four aggregates form the foundation of SQL analytics.

All Databases

-- Basic aggregates:
SELECT
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order,
  MIN(amount) AS smallest_order,
  MAX(amount) AS largest_order,
  COUNT(*) AS total_orders
FROM orders;

-- With GROUP BY:
SELECT
  category,
  SUM(price) AS total,
  AVG(price) AS avg_price,
  MIN(price) AS cheapest,
  MAX(price) AS most_expensive
FROM products
GROUP BY category
ORDER BY total DESC;

-- Conditional aggregation:
SELECT
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) AS completed_revenue,
  SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded_amount
FROM orders;

-- NULL handling: aggregates IGNORE NULLs
SELECT AVG(rating) FROM reviews;  -- NULLs are excluded
-- To include NULLs as 0:
SELECT AVG(COALESCE(rating, 0)) FROM reviews;

-- HAVING (filter after aggregation):
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Skip the Syntax Lookup

Instead of memorizing SUM / AVG / MIN / MAX syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Frequently Asked Questions