Learn how to use COUNT, SUM, and AVG to summarize an entire dataset in a single SQL query — no GROUP BY required. This lesson covers NULL behavior, combining multiple aggregates, and practical business reporting patterns from first principles.
Imagine you've just been handed a spreadsheet with 50,000 sales transactions and your manager asks three simple questions: "How many orders did we process last quarter? What was the total revenue? And what was the average order value?" You could scroll through every row counting and adding — or you could write three lines of SQL and have answers in milliseconds.
This is exactly what aggregate functions are built for. Before you need to break data into groups (which requires GROUP BY), there's a foundational skill worth mastering first: running aggregations across an entire table or result set. No grouping, no categories — just one number that summarizes everything. These "whole-table" aggregations are often the first analytical queries you'll write in a real job, and understanding them deeply will make the jump to grouped aggregations much smoother when you get there.
By the end of this lesson, you'll be able to write confident queries using COUNT, SUM, and AVG to summarize entire datasets. You'll understand why these functions behave the way they do, how NULL values affect your results, and how to combine multiple aggregates in a single query.
What you'll learn:
COUNT, SUM, and AVG work, including their edge casesGROUP BY to use aggregate functionsSELECT statementThis lesson assumes you're comfortable writing basic SELECT queries. If you haven't yet mastered selecting columns, filtering with WHERE, and reading query results, spend some time with SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries before continuing. You don't need any prior knowledge of aggregation — we're starting from the beginning.
A regular SQL expression returns one value per row. If you SELECT price FROM orders, you get back one price for every row in the table — 50,000 rows in, 50,000 values out.
An aggregate function is different. It takes a set of rows as input and collapses them into a single output value. Feed it 50,000 rows, get back one number. That's the essential idea.
SQL has five classic aggregate functions: COUNT, SUM, AVG, MIN, and MAX. This lesson focuses on the first three because they're the most frequently misunderstood and the most powerful for answering business questions quickly. MIN and MAX follow the same logic and will feel intuitive once you've internalized how COUNT, SUM, and AVG work.
Key insight
When you use an aggregate function without GROUP BY, SQL treats your entire result set as one single group. The function collapses every qualifying row into a single summary value. This is sometimes called a "scalar aggregate" — it produces a scalar (single) result.
Here's the simplest possible aggregate query, using a fictional orders table:
SELECT COUNT(*)
FROM orders;
This returns a single row containing a single number: the total count of rows in the orders table. No groups, no categories — just the total.
Throughout this lesson, we'll work with an orders table from a fictional e-commerce company. Here's what the structure looks like:
| Column | Type | Description |
|---|---|---|
order_id |
INTEGER | Unique identifier for each order |
customer_id |
INTEGER | Which customer placed the order |
order_date |
DATE | When the order was placed |
total_amount |
DECIMAL | Dollar value of the order |
status |
VARCHAR | 'completed', 'refunded', or 'pending' |
discount_applied |
DECIMAL | Dollar amount discounted (can be NULL) |
The table has 12,000 rows covering the last 24 months. Some orders have no discount applied, so discount_applied contains NULL values for those rows. This detail will matter shortly.
COUNT is the most frequently used aggregate function, and it has two importantly different forms that confuse beginners constantly.
SELECT COUNT(*)
FROM orders;
Result:
12000
COUNT(*) counts every row in the result set, regardless of what values those rows contain. NULLs don't matter. Blanks don't matter. Every row is a row.
Use COUNT(*) when your question is: "How many records are there?"
SELECT COUNT(discount_applied)
FROM orders;
Result:
4731
COUNT(column) counts only the rows where that column contains a non-NULL value. In our dataset, 4,731 orders had a discount applied; the other 7,269 had NULL in that column and were not counted.
This is enormously useful. Want to know how many orders actually received a discount? Use COUNT(discount_applied). Want to know the total number of orders? Use COUNT(*).
Warning
Beginners often use COUNT(column) when they mean COUNT(*), then wonder why their count seems low. Always ask yourself: am I counting rows, or am I counting non-NULL values in a specific column? The answer determines which form to use.
There's a third form worth knowing:
SELECT COUNT(DISTINCT customer_id)
FROM orders;
Result:
3842
This tells us how many unique customers placed at least one order. Even if a customer placed 20 orders, they only count once. You can read more about when to reach for DISTINCT in Counting and Grouping with DISTINCT vs GROUP BY: When to Use Each and Why It Matters.
SUM adds up every value in a column across all rows in the result set. It only makes sense on numeric columns.
SELECT SUM(total_amount)
FROM orders;
Result:
2847193.50
That's total revenue across all 12,000 orders: $2,847,193.50. One query, one number, immediate business value.
Here's a behavior that surprises people: SUM ignores NULL values. It doesn't treat NULL as zero — it simply skips it.
SELECT SUM(discount_applied)
FROM orders;
Only the 4,731 rows with actual discount values are summed. The 7,269 rows where discount_applied is NULL contribute nothing to the total — not zero, just nothing. In most cases this is exactly what you want (a missing discount is nothing), but it's important to know this is what's happening under the hood.
Tip
If your business logic says NULL should be treated as zero — perhaps because a NULL discount means "no discount was offered, which is the same as $0" — you can use COALESCE to convert NULLs before summing: SUM(COALESCE(discount_applied, 0)). This produces the same result when NULLs truly mean zero, but it makes your intent explicit. Learn more about handling NULL values in NULL Handling in SQL: IS NULL, COALESCE, and NULLIF.
You can combine aggregates with WHERE to answer more targeted questions:
SELECT SUM(total_amount)
FROM orders
WHERE status = 'completed';
Result:
2504871.25
This is total revenue from completed orders only. Refunded and pending orders are excluded by the WHERE clause before SUM ever runs. The aggregate function sees only the rows that pass the filter.
This pattern — filter first, then aggregate — is one of the most common in analytical SQL. Always remember: WHERE runs before aggregate functions execute.
AVG calculates the arithmetic mean: the sum of all values divided by the count of non-NULL values.
SELECT AVG(total_amount)
FROM orders;
Result:
237.27
Average order value: $237.27. Simple and readable.
This is where AVG can mislead you if you're not paying attention. Like SUM, AVG ignores NULL values — but the implications are more subtle.
Consider this scenario with a five-row sample:
| order_id | discount_applied |
|---|---|
| 1 | 10.00 |
| 2 | 25.00 |
| 3 | NULL |
| 4 | NULL |
| 5 | 15.00 |
SELECT AVG(discount_applied) FROM orders_sample;
Result:
16.67
SQL computed (10 + 25 + 15) / 3 = 16.67. It divided by 3 — the count of non-NULL values — not by 5 (the total number of rows).
Is this right? It depends entirely on what your NULLs mean. If NULL means "this order was not discounted," then the average discount across all orders should probably be calculated as (10 + 25 + 0 + 0 + 15) / 5 = 10.00, which you'd get with:
SELECT AVG(COALESCE(discount_applied, 0)) FROM orders_sample;
Warning
Always stop and ask what NULL means in your data before writing a SUM or AVG. NULL could mean "unknown," "not applicable," "zero," or "data wasn't collected." The right SQL depends on the right interpretation. Getting this wrong produces quietly incorrect results — no error message, just a misleading number.
You're not limited to one aggregate per query. A single SELECT statement can compute multiple aggregate expressions simultaneously, which is one of the most practical patterns in data analysis.
SELECT
COUNT(*) AS total_orders,
COUNT(DISTINCT customer_id) AS unique_customers,
SUM(total_amount) AS total_revenue,
AVG(total_amount) AS avg_order_value,
COUNT(discount_applied) AS orders_with_discount
FROM orders
WHERE status = 'completed';
Result:
| total_orders | unique_customers | total_revenue | avg_order_value | orders_with_discount |
|---|---|---|---|---|
| 10542 | 3619 | 2504871.25 | 237.61 | 4012 |
This single query answers five different business questions in one pass against the database. It's efficient, readable, and exactly the kind of summary a manager or analyst would want as a starting point.
Notice the use of aliases (AS total_orders, AS total_revenue, etc.) to give each result a meaningful name. Without aliases, your output columns would be named COUNT(*), SUM(total_amount), and so on — technically correct but harder to read. Naming your outputs well is a professional habit worth building early. You can explore this further in Using SQL Aliases Effectively: Naming Columns and Tables for Readable, Maintainable Queries.
If you've heard of GROUP BY before, you might be wondering: when do you need it, and when can you leave it out?
The short answer: you need GROUP BY when you want separate aggregate results for each category in your data. You leave it out when you want a single aggregate result across the whole dataset.
Without GROUP BY:
-- One total across all orders
SELECT SUM(total_amount) AS total_revenue
FROM orders;
Result: one row, one number.
With GROUP BY:
-- One total per status category
SELECT status, SUM(total_amount) AS total_revenue
FROM orders
GROUP BY status;
Result: three rows — one for 'completed', one for 'refunded', one for 'pending'.
When you write an aggregate without GROUP BY, SQL implicitly treats the entire result set as a single group. You're not grouping by anything, so everything collapses into one answer. This is simpler, and it's the right tool when the question is about the whole dataset rather than about categories within it.
Once you're ready to explore grouped aggregation, Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners is the natural next step.
Note
You cannot mix regular column references and aggregate functions in a SELECT without GROUP BY. SELECT status, SUM(total_amount) FROM orders will throw an error (or produce unpredictable results, depending on your database). If you want both a column value and an aggregate, you must use GROUP BY to tell SQL how to collapse the rows.
Let's put everything together in a realistic scenario. Your manager asks for a quick health check on last month's orders. You need to produce:
Here's the query:
SELECT
COUNT(*) AS total_orders,
COUNT(DISTINCT customer_id) AS unique_customers,
SUM(CASE WHEN status = 'completed'
THEN total_amount ELSE 0 END) AS completed_revenue,
AVG(CASE WHEN status = 'completed'
THEN total_amount END) AS avg_completed_order,
SUM(COALESCE(discount_applied, 0)) AS total_discounts_given
FROM orders
WHERE order_date >= '2024-03-01'
AND order_date < '2024-04-01';
Notice the CASE WHEN inside the aggregates. This is a powerful pattern: you can conditionally include or exclude values within a single aggregate, allowing you to compute multiple "sliced" aggregates in one pass without splitting the query. The CASE WHEN inside AVG returns NULL for non-completed orders, which AVG then ignores — giving you the average only for completed orders while still counting all orders in COUNT(*).
This technique is explored in much more depth in Combining Aggregates with Conditional Logic: GROUP BY, HAVING, and CASE WHEN in Practice.
Work through these exercises using a database you have access to (any database with a table of transactions, sales, or similar numeric data will work, or you can create a small test table).
Exercise 1 — Basic counts: Write a query that returns:
Exercise 2 — Revenue summary:
Write a single query that returns total revenue (SUM), average transaction value (AVG), and the count of transactions, all in one result row.
Exercise 3 — Filtered aggregation:
Add a WHERE clause to your Exercise 2 query to restrict the results to a specific date range or category. Observe how the numbers change.
Exercise 4 — NULL awareness:
Find a column in your table that contains some NULL values. Write two AVG queries: one using AVG(column) and one using AVG(COALESCE(column, 0)). Compare the results and explain why they differ.
Exercise 5 — Combined report: Write a single query with at least four aggregate expressions, each with a meaningful alias, that answers a realistic question about your dataset.
Tip
If you don't have a database handy, most SQL learning environments (like SQLiteOnline, DB Fiddle, or any local PostgreSQL or MySQL installation) let you create a test table with a few INSERT statements and run queries against it immediately. Building tiny test datasets by hand is a great way to verify your understanding of edge cases.
-- This will fail or return wrong results
SELECT customer_id, COUNT(*)
FROM orders;
If you want a count per customer, you need GROUP BY customer_id. If you want just the total count, remove customer_id from the SELECT. You can't have both without telling SQL how to reconcile the multiple customer_id values.
They're not. Always check whether your column has NULLs before deciding which form to use. Run COUNT(*) - COUNT(column) to discover how many NULL values exist in a column — if the result is greater than zero, the two forms will give different answers.
This produces a "per-discount" average, not a "per-order" average:
AVG(discount_applied) -- divides by count of non-NULL rows only
If you want an average over all orders (treating no discount as zero):
AVG(COALESCE(discount_applied, 0)) -- divides by total row count
SUM('completed') doesn't make sense and will produce an error. SUM and AVG require numeric data types. Use COUNT when you need to count occurrences of text values.
If you filter with WHERE status = 'completed', your aggregate only sees completed rows. This is usually what you want, but if you're getting unexpectedly low counts or sums, check whether your WHERE clause is excluding rows you wanted to include. If you need to filter after aggregation, that's what HAVING is for — but HAVING applies to grouped results, which is a topic for when you move into GROUP BY territory. See Filtering Groups After Aggregation: Writing HAVING Clauses That Answer Real Business Questions when you're ready.
Key insight
When debugging an aggregate query that returns a suspicious number, work backwards. First verify what rows your WHERE clause is actually returning (run the query without the aggregate). Then add the aggregate back in. This two-step debugging approach catches most mistakes quickly.
You've now covered the foundational mechanics of SQL aggregate functions without GROUP BY. Here's what you've built:
COUNT(*) counts every row. COUNT(column) counts only non-NULL values. COUNT(DISTINCT column) counts unique values.SUM adds up numeric values, ignoring NULLs. Use COALESCE when NULLs should be treated as zero.AVG computes the mean of non-NULL values, dividing by the count of non-NULL rows — not the total row count.SELECT to build multi-metric summary reports in one efficient query.WHERE filters rows before aggregation runs. You cannot mix plain column references and aggregates without GROUP BY.The most important conceptual shift is understanding that aggregate functions operate on a set of rows, not individual values. When there's no GROUP BY, that set is your entire result set. Every design decision — which function, whether to use DISTINCT, how to handle NULLs — follows from that understanding.
Where to go next:
Once you're comfortable with whole-table aggregation, the next logical step is learning to break results into groups with GROUP BY. Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners continues exactly where this lesson ends. After that, you'll want to learn how to filter those groups with HAVING in Ranking and Filtering Groups with HAVING: Writing Conditional Aggregates That Go Beyond WHERE.
As your queries grow more complex — combining aggregates across multiple tables, embedding aggregates inside subqueries, using window functions — everything builds on the foundation you've established here. The mechanics of COUNT, SUM, and AVG never change; only the context they're used in grows more sophisticated.