SQL Aggregate Functions: COUNT, SUM, AVG, MIN and MAX

One query, five summaries of the 120 employees' salaries:

SELECT COUNT(*)            AS employees,
       SUM(salary)         AS total_salary,
       ROUND(AVG(salary))  AS avg_salary,
       MIN(salary)         AS lowest,
       MAX(salary)         AS highest
FROM employees;

▶ Run this query in the SQL Playground

Without GROUP BY, the whole result is one group, so the query returns exactly one row however many rows the table has.

The three forms of COUNT answer different questions. Orders that have not shipped have NULL in shipped_date, which shows the difference:

SELECT COUNT(*)                AS all_orders,
       COUNT(shipped_date)     AS orders_with_shipped_date,
       COUNT(DISTINCT user_id) AS distinct_customers,
       COUNT(DISTINCT status)  AS distinct_statuses
FROM orders;

▶ Run this query in the SQL Playground

COUNT(1) behaves exactly like COUNT(*); neither is faster in modern databases.

Revenue and average order value for delivered orders:

SELECT COUNT(*) AS delivered_orders,
       ROUND(SUM(total), 2) AS revenue,
       ROUND(AVG(total), 2) AS avg_order_value
FROM orders
WHERE status = 'delivered';

▶ Run this query in the SQL Playground

WHERE runs before the aggregates, so only the delivered orders are added up. Rounding with ROUND keeps long decimals out of reports.

MIN and MAX work on any type that can be sorted. On dates they give the first and last; on text, the first and last alphabetically:

SELECT MIN(order_date) AS first_order,
       MAX(order_date) AS latest_order,
       MIN(total)      AS smallest_order,
       MAX(total)      AS largest_order
FROM orders;

▶ Run this query in the SQL Playground

SELECT MIN(name) AS first_alphabetically,
       MAX(name) AS last_alphabetically
FROM products;

▶ Run this query in the SQL Playground

MIN and MAX return a value, not the row it belongs to. To find which product is cheapest, use ORDER BY price LIMIT 1 or WHERE price = (SELECT MIN(price) FROM products).

Every aggregate except COUNT(*) skips NULL values. That is usually what you want, but it changes averages. The sample rows below include a missing bonus:

WITH bonuses(name, bonus) AS (
  VALUES ('Aditi', 15000), ('Rohan', NULL), ('Priya', 8000), ('Karan', NULL), ('Sneha', 0)
)
SELECT COUNT(*)                AS people,
       COUNT(bonus)            AS bonuses_recorded,
       SUM(bonus)              AS total,
       ROUND(AVG(bonus), 2)    AS avg_recorded,
       AVG(COALESCE(bonus, 0)) AS avg_per_person
FROM bonuses;

▶ Run this query in the SQL Playground

AVG divides by the 3 recorded values, not by all 5 people. If a missing bonus should count as zero, say so with COALESCE.

Aggregates over no rows

When nothing matches, COUNT returns 0 but SUM, AVG, MIN and MAX return NULL. There are no orders from 2025 in the Playground:

SELECT COUNT(*)   AS orders,
       SUM(total) AS revenue,
       MAX(total) AS largest,
       COALESCE(SUM(total), 0) AS revenue_or_zero
FROM orders
WHERE order_date >= '2025-01-01';

▶ Run this query in the SQL Playground

GROUP BY splits the rows into groups and runs the aggregates once per group. Headcount and average salary per department:

SELECT department,
       COUNT(*) AS employees,
       ROUND(AVG(salary)) AS avg_salary,
       MAX(salary) AS top_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

▶ Run this query in the SQL Playground

Filter rows with WHERE before grouping, and filter groups with HAVING after it. Departments with at least 12 employees:

SELECT department, COUNT(*) AS employees
FROM employees
GROUP BY department
HAVING COUNT(*) >= 12
ORDER BY employees DESC;

▶ Run this query in the SQL Playground

WHERE cannot contain an aggregate because it runs before the groups exist. The GROUP BY and HAVING lessons go further.

A CASE inside an aggregate counts or sums only the rows you choose, so one query can produce several figures side by side:

SELECT COUNT(*) AS orders,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status IN ('cancelled', 'refunded') THEN 1 ELSE 0 END) AS lost,
       ROUND(SUM(CASE WHEN status = 'delivered' THEN total ELSE 0 END), 2) AS delivered_revenue
FROM orders;

▶ Run this query in the SQL Playground

Most databases also have an aggregate that joins text: GROUP_CONCAT in SQLite and MySQL, STRING_AGG in PostgreSQL and SQL Server, LISTAGG in Oracle:

SELECT department,
       GROUP_CONCAT(first_name, ', ') AS first_names
FROM employees
WHERE department IN ('Design', 'Product')
GROUP BY department;

▶ Run this query in the SQL Playground

Six errors and how to avoid them.

SELECT department
FROM employees
WHERE AVG(salary) > 130000
GROUP BY department;

SELECT department
FROM employees
GROUP BY department
HAVING AVG(salary) > 130000;

SELECT COUNT(shipped_date) AS orders
FROM orders;   -- 103

SELECT COUNT(*) AS orders
FROM orders;   -- 200

SELECT department, first_name, AVG(salary)
FROM employees
GROUP BY department;

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

SELECT SUM(total) FROM orders
WHERE order_date >= '2025-01-01';

SELECT COALESCE(SUM(total), 0) FROM orders
WHERE order_date >= '2025-01-01';

SELECT AVG(dept_avg)
FROM (SELECT AVG(salary) AS dept_avg
      FROM employees GROUP BY department) t;

SELECT AVG(salary)
FROM employees;

SELECT DISTINCT COUNT(user_id)
FROM orders;   -- 200

SELECT COUNT(DISTINCT user_id)
FROM orders;   -- 114

6 exercises on the real Playground tables.

Write each query yourself first. Every solution has been run against the Playground database.

1. 1Easy Find the number of products, the average price and the price of the cheapest and most expensive product. Table: products

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS products,
          ROUND(AVG(price), 2) AS avg_price,
          MIN(price) AS cheapest,
          MAX(price) AS most_expensive
   FROM products;

   ▶ Run this query in the SQL Playground

   Without GROUP BY, the whole table is one group.

2. 2Easy How many users are there, and how many different cities and countries do they live in? Table: users

   Show solution

   SQLite · runs in the Playground

   SELECT COUNT(*) AS users,
          COUNT(DISTINCT city) AS cities,
          COUNT(DISTINCT country) AS countries
   FROM users;

   ▶ Run this query in the SQL Playground

   COUNT(DISTINCT ...) counts each value once.

3. 3Medium For each rating from 1 to 5, show the number of reviews and the average helpful votes. Table: reviews

   Show solution

   SQLite · runs in the Playground

   SELECT rating,
          COUNT(*) AS reviews,
          ROUND(AVG(helpful_votes), 1) AS avg_helpful_votes
   FROM reviews
   GROUP BY rating
   ORDER BY rating DESC;

   ▶ Run this query in the SQL Playground

   One row per rating value.

4. 4Medium Show the units sold and revenue per product category, highest revenue first. Table: order_items, products

   Show solution

   SQLite · runs in the Playground

   SELECT p.category,
          SUM(oi.quantity) AS units,
          ROUND(SUM(oi.subtotal), 2) AS revenue
   FROM order_items oi
   JOIN products p ON p.id = oi.product_id
   GROUP BY p.category
   ORDER BY revenue DESC;

   ▶ Run this query in the SQL Playground

   Join first, then aggregate the joined rows per category.

5. 5Medium Show the departments whose lowest salary is above 50,000. Table: employees

   Show solution

   SQLite · runs in the Playground

   SELECT department, MIN(salary) AS lowest_salary
   FROM employees
   GROUP BY department
   HAVING MIN(salary) > 50000
   ORDER BY lowest_salary DESC;

   ▶ Run this query in the SQL Playground

   A condition on an aggregate goes in HAVING.

6. 6Hard For each payment method, show the number of payments, the total amount and the share of payments that succeeded. Table: payments

   Show solution

   SQLite · runs in the Playground

   SELECT method,
          COUNT(*) AS payments,
          ROUND(SUM(amount), 2) AS total_amount,
          ROUND(100.0 * SUM(CASE WHEN status = 'success' THEN 1 ELSE 0 END) / COUNT(*), 1) AS success_pct
   FROM payments
   GROUP BY method
   ORDER BY payments DESC;

   ▶ Run this query in the SQL Playground

   A conditional SUM divided by COUNT(*) gives a percentage per group.