0Pricing
SQL Academy · Lesson

COUNT, SUM, AVG, MIN, MAX

Use the five core aggregate functions, understand COUNT(*) vs COUNT(column), and know how NULLs are treated by each.

COUNT, SUM, AVG, MIN, MAX is a free SQL Academy lesson on CoddyKit — lesson 1 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

What Aggregates Do

An aggregate function collapses many rows into one value. Examples: total revenue, average rating, max date.

COUNT

Counts rows. Two flavours:

SELECT COUNT(*)         FROM users;          -- counts ALL rows
SELECT COUNT(email)     FROM users;          -- counts rows where email IS NOT NULL
SELECT COUNT(DISTINCT country) FROM users;   -- distinct non-NULL countries

SUM

Sums a numeric column. NULLs are skipped:

SELECT SUM(total) FROM orders;
SELECT SUM(total) FROM orders WHERE user_id = 42;

AVG

Computes the arithmetic mean. NULLs are skipped — so AVG(x) equals SUM(x)/COUNT(x), not SUM(x)/COUNT(*):

SELECT AVG(rating) FROM reviews;

MIN and MAX

Smallest and largest non-NULL value. Works on numbers, dates, strings — anything comparable:

SELECT MIN(created_at), MAX(created_at) FROM orders;
SELECT MIN(name), MAX(name) FROM users;   -- alphabetic

Aggregates and NULL

All aggregates except COUNT(*) skip NULL inputs. AVG, SUM, MIN, MAX return NULL when no non-NULL inputs exist.

-- Suppose the table is empty or all rating is NULL:
SELECT AVG(rating) FROM reviews;   -- NULL

Combining Aggregates

Multiple aggregates in one query — all see the same group:

SELECT COUNT(*)      AS orders,
       SUM(total)    AS revenue,
       AVG(total)    AS avg_order,
       MIN(total)    AS smallest,
       MAX(total)    AS biggest
FROM orders
WHERE created_at >= NOW() - INTERVAL '30 days';

DISTINCT Inside Aggregates

Apply DISTINCT before aggregating:

SELECT COUNT(DISTINCT user_id) AS unique_buyers,
       SUM(DISTINCT total)     AS sum_of_distinct_totals
FROM orders;

Aggregates Without GROUP BY

When you use an aggregate without GROUP BY, the entire result set is one big group — you get one row out.

Filtering Before Aggregation

WHERE narrows the input rows before they're aggregated:

-- Average of only large orders:
SELECT AVG(total) FROM orders WHERE total > 100;

FILTER Clause (PostgreSQL)

Add per-aggregate filters with the FILTER clause:

SELECT
  COUNT(*)                                AS total_orders,
  COUNT(*) FILTER (WHERE status = 'paid')  AS paid_orders,
  SUM(total) FILTER (WHERE status = 'paid') AS paid_revenue
FROM orders;

Recap

The five core aggregates: COUNT, SUM, AVG, MIN, MAX.

  • COUNT(*) counts rows; COUNT(col) skips NULL
  • Other aggregates skip NULL inputs
  • Combine with WHERE / FILTER for conditional aggregation

Quick Check

Suppose rating is NULL for 100 rows out of 500. What does AVG(rating) divide by?

Frequently asked questions

Is the “COUNT, SUM, AVG, MIN, MAX” lesson free?

Yes — the full text of “COUNT, SUM, AVG, MIN, MAX” is free to read here on the web, and the SQL Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Academy course, upgrade to CoddyKit PRO.

What will I learn in “COUNT, SUM, AVG, MIN, MAX”?

Use the five core aggregate functions, understand COUNT(*) vs COUNT(column), and know how NULLs are treated by each. You practise SQL Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Academy?

No prior experience is required. SQL Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 1 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “COUNT, SUM, AVG, MIN, MAX” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Academy lesson?

Yes. Every SQL Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. COUNT, SUM, AVG, MIN, MAX
  2. GROUP BY Single and Multiple Columns
  3. HAVING vs WHERE
  4. Common Pitfalls: NULLs in Aggregates
← Back to SQL Academy