Grouping and Summarizing Data: COUNT, SUM, AVG, and GROUP BY for Beginners

Introduction

Imagine you're handed a spreadsheet with 50,000 rows of sales transactions. Your manager walks over and asks: "How much revenue did each product category bring in last quarter?" You could scroll through every row manually — or you could write a single SQL query that answers the question in milliseconds. That's exactly what aggregate functions and GROUP BY are for, and by the end of this lesson, you'll be writing those queries with confidence.

Aggregation is one of the most powerful ideas in SQL. Instead of looking at individual rows, you collapse them into summaries — totals, averages, counts — that reveal patterns you'd never see in the raw data. This is the skill that turns a database into a source of actual business intelligence. Whether you're analyzing customer behavior, tracking inventory, or building a dashboard, you'll use these tools constantly.

This lesson covers the four most essential aggregation tools in SQL: COUNT, SUM, AVG, and GROUP BY. We'll build up from first principles, starting with what these functions actually do, then combining them into increasingly powerful queries. Every example uses a realistic dataset so the patterns will feel immediately familiar when you encounter them on the job.

What you'll learn:

• How COUNT, SUM, and AVG work and when to use each one
• How GROUP BY lets you calculate summaries for each category in your data
• How to combine aggregate functions with GROUP BY to answer real analytical questions
• How to avoid the most common mistakes beginners make with aggregation
• How filtering interacts with grouped queries

---

Prerequisites

This lesson assumes you're comfortable selecting data from a table and filtering rows with WHERE. If those concepts are new to you, start with SQL Basics: Master SELECT, FROM, WHERE Clauses and Build Your First Queries before continuing here. You don't need any programming background — just a basic understanding of what a table, row, and column are.

---

The Dataset We'll Use

Throughout this lesson, we'll work with an orders table from a fictional e-commerce company called Brightleaf Goods. Here's what the table looks like:

orders
--------------------------------------------------------------
order_id  | customer_id | category    | amount  | order_date
----------+-------------+-------------+---------+-----------
1001      | 42          | Electronics | 299.99  | 2024-01-05
1002      | 17          | Apparel     | 49.99   | 2024-01-06
1003      | 42          | Books       | 14.99   | 2024-01-06
1004      | 88          | Electronics | 599.00  | 2024-01-08
1005      | 17          | Apparel     | 89.99   | 2024-01-09
1006      | 55          | Books       | 22.50   | 2024-01-10
1007      | 88          | Electronics | 149.99  | 2024-01-11
1008      | 42          | Apparel     | 34.99   | 2024-01-12

In a real-world scenario this table would have tens of thousands of rows, but eight rows is enough to see exactly what's happening at each step.

---

What Is an Aggregate Function?

An aggregate function takes a whole column — or a group of rows within a column — and computes a single value from them. Think of it like a calculator that works on an entire set of numbers at once.

Regular SQL works row by row: you ask for amount and you get 8 rows back, one value per row. Aggregate functions flip that model: you ask for SUM(amount) and you get back one number that represents all the rows combined.

The four aggregate functions you'll use most often are:

Function	What it does
COUNT()	Counts rows or non-null values
SUM()	Adds up all values in a column
AVG()	Calculates the arithmetic mean
MIN() / MAX()	Finds the smallest or largest value

We'll focus on the first three in depth, then briefly touch on MIN and MAX.

---

COUNT: Counting Rows

COUNT answers the question: how many? Let's start with the simplest possible version:

SELECT COUNT(*)
FROM orders;

Result:

count
-----
8

The * means "count every row, no matter what." This is the most common form of COUNT and the one you'll use most often to find out how many records exist in a table.

You can also count a specific column:

SELECT COUNT(amount)
FROM orders;

This gives the same result here — 8 — because every row has a value in amount. But the behavior differs when a column contains NULL values. COUNT(column_name) skips nulls; COUNT(*) never does.

You can also count only distinct values. If you want to know how many unique customers placed orders:

SELECT COUNT(DISTINCT customer_id)
FROM orders;

Result:

count
-----
4

Even though there are 8 orders, only 4 unique customer IDs appear in the data.

---

SUM: Adding It All Up

SUM answers: what's the total? It adds up every value in a numeric column.

SELECT SUM(amount)
FROM orders;

Result:

sum
--------
1261.44

That's the total revenue across all 8 orders. Simple, direct, and immediately useful. You'd use this same pattern to calculate total payroll, total inventory value, or total page views — any scenario where you need a grand total.

---

AVG: Finding the Average

AVG computes the arithmetic mean — it adds all values and divides by the count of non-null values.

SELECT AVG(amount)
FROM orders;

Result:

avg
-----------
157.680000

The average order value at Brightleaf Goods is about $157.68. This is a useful baseline metric — you might compare it across time periods, product categories, or customer segments.

You can combine multiple aggregate functions in one query:

SELECT
  COUNT(*)   AS total_orders,
  SUM(amount)  AS total_revenue,
  AVG(amount)  AS avg_order_value
FROM orders;

Result:

total_orders | total_revenue | avg_order_value
-------------+---------------+----------------
8            | 1261.44       | 157.680000

The AS keyword creates an alias — a human-readable name for the column in your results. Always alias your aggregate columns; without aliases, most databases return generic names like count or sum(amount) that are harder to work with downstream.

---

GROUP BY: Breaking Summaries into Categories

So far, every aggregate query has collapsed all 8 rows into a single summary row. That's useful, but the real power arrives when you combine aggregation with GROUP BY.

GROUP BY tells SQL: before you aggregate, split the rows into buckets based on this column, then apply the aggregate function to each bucket separately.

Here's how you'd answer "How much did each product category sell?":

SELECT
  category,
  SUM(amount) AS total_revenue
FROM orders
GROUP BY category;

Result:

category    | total_revenue
------------+--------------
Apparel     | 174.97
Books       | 37.49
Electronics | 1048.98

SQL has done something elegant here: it split the 8 rows into three groups — one per category — then summed the amount values within each group. You get one result row per group.

Let's add COUNT to see both how many orders and the total revenue per category:

SELECT
  category,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value
FROM orders
GROUP BY category;

Result:

category    | order_count | total_revenue | avg_order_value
------------+-------------+---------------+----------------
Apparel     | 3           | 174.97        | 58.323333
Books       | 2           | 37.49         | 18.745000
Electronics | 3           | 1048.98       | 349.660000

Now you're getting somewhere. Electronics has the same number of orders as Apparel, but the average order value is six times higher. That's a meaningful business insight, and you extracted it in six lines of SQL.

---

Grouping by Multiple Columns

You can group by more than one column at a time. SQL will create a separate bucket for every unique combination of values across all the grouped columns.

Suppose you want revenue broken down by both category and customer:

SELECT
  customer_id,
  category,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id, category
ORDER BY customer_id, category;

Result:

customer_id | category    | order_count | total_spent
------------+-------------+-------------+------------
17          | Apparel     | 2           | 139.98
42          | Apparel     | 1           | 34.99
42          | Books       | 1           | 14.99
42          | Electronics | 1           | 299.99
55          | Books       | 1           | 22.50
88          | Electronics | 2           | 748.99

Notice we also added ORDER BY to make the results easier to read. The ORDER BY clause sorts the final output — it runs after GROUP BY in SQL's logical processing order, so you can sort by any column in your result set. For a deeper treatment of filtering and sorting options, see Advanced SQL Filtering and Sorting: Mastering WHERE, ORDER BY, and Query Optimization.

---

Filtering Before and After Grouping

There are two different places you can filter in an aggregation query, and they do different things.

Filtering Before Grouping: WHERE

A WHERE clause filters rows before the groups are formed. If you only want to analyze orders from January 8th onward:

SELECT
  category,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_revenue
FROM orders
WHERE order_date >= '2024-01-08'
GROUP BY category;

The database first discards all rows where order_date is before January 8th, then groups and aggregates what's left. The early orders (1001, 1002, 1003) won't appear in any group.

Filtering After Grouping: HAVING

Sometimes you need to filter based on the result of an aggregation. That's what HAVING is for. Suppose you want to see only categories that have generated more than $100 in revenue:

SELECT
  category,
  SUM(amount) AS total_revenue
FROM orders
GROUP BY category
HAVING SUM(amount) > 100;

Result:

category    | total_revenue
------------+--------------
Apparel     | 174.97
Electronics | 1048.98

Books only generated $37.49, so it's filtered out. Notice that HAVING references SUM(amount) — the aggregated value — whereas WHERE can only reference raw column values. You cannot put an aggregate function inside a WHERE clause; that's what HAVING is specifically designed for.

---

The Logical Order of SQL Execution

Understanding why certain things work in SQL becomes much easier when you know the order the database actually processes your query. It's different from the order you write it:

1. FROM — identify the source table
2. WHERE — filter individual rows
3. GROUP BY — form groups
4. HAVING — filter groups
5. SELECT — compute the output columns (including aggregates)
6. ORDER BY — sort the results

This is why you can use an alias defined in SELECT in your ORDER BY clause (it runs after), but not in your WHERE or HAVING clause in most databases (those run before SELECT is evaluated). Keep this sequence in mind whenever a query behaves unexpectedly.

---

Hands-On Exercise

Try these exercises using the orders table described earlier. If you have access to a database tool, create the table and insert the sample rows. If not, reason through the expected output before checking below.

Exercise 1: Write a query that returns the total number of orders placed and the overall average order value across the entire dataset.

Exercise 2: Write a query that shows, for each customer_id, the total amount they've spent and the number of orders they've placed. Order the results by total amount spent, highest to lowest.

Exercise 3: Write a query that returns only customers who have placed more than one order. (Hint: you'll need HAVING.)

Exercise 4: Write a query that shows revenue by category, but only for orders placed on or after 2024-01-08. Order results alphabetically by category.

---

Expected results:

Exercise 1:

SELECT COUNT(*) AS total_orders, AVG(amount) AS avg_order_value
FROM orders;
-- total_orders: 8, avg_order_value: ~157.68

Exercise 2:

SELECT
  customer_id,
  COUNT(*)    AS order_count,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

Exercise 3:

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

Exercise 4:

SELECT
  category,
  SUM(amount) AS total_revenue
FROM orders
WHERE order_date >= '2024-01-08'
GROUP BY category
ORDER BY category;

---

Common Mistakes & Troubleshooting

Mistake 1: Selecting a non-aggregated column that isn't in GROUP BY

This is the single most common error beginners make:

-- This will fail in most databases
SELECT category, order_date, SUM(amount)
FROM orders
GROUP BY category;

order_date is in SELECT but not in GROUP BY and not wrapped in an aggregate function. The database doesn't know which order_date to show for each category group — there are multiple rows per category, each with a different date. Fix it by either adding order_date to GROUP BY or removing it from SELECT.

Mistake 2: Using WHERE instead of HAVING for aggregate filters

-- This will fail
SELECT category, SUM(amount)
FROM orders
WHERE SUM(amount) > 100
GROUP BY category;

You can't use an aggregate function in a WHERE clause because WHERE runs before aggregation happens — there's no sum to compare against yet. Use HAVING instead.

Mistake 3: Confusing COUNT(*) with COUNT(column)

SELECT COUNT(*) AS total_rows, COUNT(amount) AS rows_with_amount
FROM orders;

If amount had any NULL values, these two numbers would differ. COUNT(*) counts every row; COUNT(column) only counts rows where that column has a non-null value. When you want a true row count, use COUNT(*).

Mistake 4: Forgetting that AVG ignores NULLs

If you have missing data in your numeric column and you need to treat NULL as zero, use AVG(COALESCE(amount, 0)). The COALESCE function substitutes a default value when it encounters NULL. Read more about this pattern in Understanding SQL NULL Handling: COALESCE, NULLIF, and IS NULL for Reliable Data Queries.

Mistake 5: Expecting a specific sort order without ORDER BY

GROUP BY does not guarantee any particular order in your results. Different databases (and even different query executions on the same database) may return groups in different sequences. If order matters — and in reports it usually does — always add an explicit ORDER BY.

---

Summary & Next Steps

You now have a solid foundation in SQL aggregation. Let's recap what you've learned:

• COUNT(*) counts rows; COUNT(column) counts non-null values; COUNT(DISTINCT column) counts unique values
• SUM(column) totals all numeric values; AVG(column) finds the mean, ignoring nulls
• GROUP BY splits rows into buckets before aggregating, giving you one result row per unique group
• WHERE filters rows before grouping; HAVING filters groups after aggregation
• Every column in SELECT must either appear in GROUP BY or be wrapped in an aggregate function
• SQL processes clauses in a specific order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

These tools are the backbone of data analysis in SQL. You'll use them in virtually every reporting or analytics query you write.

Where to go next:

Once you're comfortable with the basics here, there's a lot of territory to explore. The natural next step is learning how to combine aggregation with data from multiple tables — SQL JOINs Explained with Real-World Examples will show you how to pull related data together before you aggregate it. When you're ready to push your aggregation skills further, Master SQL Aggregate Functions: Advanced GROUP BY, HAVING, and Performance Optimization covers more sophisticated patterns including ROLLUP, CUBE, and performance considerations for large datasets. And if you find yourself needing to run aggregations inside larger queries, Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture is the next logical step.

The most important thing you can do right now is write queries against real data. The pattern clicks when you see it answer an actual question you had.