SQL Aggregations Explained: COUNT, SUM, AVG, GROUP BY and HAVING with Examples
In Topic 2 you learned to retrieve and filter individual rows. That is useful — but it is not where the real analysis happens. A manager does not want to see 10,000 individual order rows. They want to know: what is our total revenue this year? Which region is most profitable? Which product category has the highest average order value?
Those questions cannot be answered by looking at individual rows. They require aggregation — combining multiple rows into a single summary figure. This topic covers the five aggregate functions every analyst uses daily, along with GROUP BY and HAVING which control how aggregations are grouped and filtered.
What Is Aggregation and Why Does It Matter
Aggregation means collapsing multiple rows of data into a single calculated value. Instead of seeing every individual sale, you see the total. Instead of every order’s profit, you see the average. Instead of each transaction, you see a count.
In pandas you did this with groupby() and agg() in Module 1. SQL does the same thing — but directly inside the database, before the data even reaches Python. This matters for performance. Aggregating 500,000 rows in SQL and returning a 4-row summary is dramatically faster than loading all 500,000 rows into Python and then aggregating them.
In a professional analyst workflow the rule is simple: aggregate in SQL, visualise and present in Python. Do the heavy lifting where the data lives.
The Five Aggregate Functions
Before combining these with GROUP BY, understand what each function does on its own when applied to an entire table.
COUNT — How Many Rows
COUNT counts the number of rows. It is the most frequently used aggregate function in day-to-day analyst work.
-- How many orders are in the dataset?
query("""
SELECT COUNT(*) AS total_orders
FROM superstore
""")Output:
| total_orders |
|---|
| 9994 |
COUNT(*) counts all rows including nulls. COUNT(column_name) counts only rows where that column is not null — a useful distinction when checking data completeness:
-- How many rows have a valid postal code vs total rows?
query("""
SELECT
COUNT(*) AS total_rows,
COUNT(postal_code) AS rows_with_postal_code
FROM superstore
""")If these two numbers differ, you have missing values in that column — which is exactly what you want to know before any analysis.
SUM — Total Value
SUM adds up all values in a numeric column. Used constantly for revenue, profit, quantity, and any other additive metric.
-- What is the total revenue and total profit across all orders?
query("""
SELECT
ROUND(SUM(sales), 2) AS total_revenue,
ROUND(SUM(profit), 2) AS total_profit
FROM superstore
""")Output:
| total_revenue | total_profit |
|---|---|
| 2297200.86 | 286397.02 |
ROUND(value, 2) keeps results to two decimal places. Always use it with financial figures — raw floating point results from SQL can be messy.
AVG — Average Value
AVG calculates the mean of a numeric column. Useful for understanding typical order size, average margin, average quantity, and so on.
-- What is the average order value and average profit per transaction?
query("""
SELECT
ROUND(AVG(sales), 2) AS avg_order_value,
ROUND(AVG(profit), 2) AS avg_profit
FROM superstore
""")Mean vs median reminder: AVG calculates the mean, which is pulled upward by large outliers. A small number of very large orders can make the average look higher than what a typical order is worth. In Module 1 you compared mean and median in pandas — keep that in mind when interpreting AVG results from SQL.
MIN and MAX — Smallest and Largest Values
MIN and MAX return the lowest and highest values in a column. Useful for understanding the range of your data and spotting outliers.
-- What is the range of order values in the dataset?
query("""
SELECT
ROUND(MIN(sales), 2) AS smallest_order,
ROUND(MAX(sales), 2) AS largest_order,
ROUND(MIN(profit), 2) AS worst_profit,
ROUND(MAX(profit), 2) AS best_profit
FROM superstore
""")Output:
| smallest_order | largest_order | worst_profit | best_profit |
|---|---|---|---|
| 0.44 | 22638.48 | -6599.98 | 8399.98 |
The worst profit being nearly -$6,600 on a single order is a significant finding. MAX and MIN surface these extremes instantly — without having to sort through thousands of rows manually.
GROUP BY — Aggregating by Category
Aggregate functions on their own give you a single number for the entire table. GROUP BY breaks that down by a category — giving you one summary row per group instead of one summary row for everything.
Syntax:
SELECT column, AGG_FUNCTION(column)<br>FROM table<br>GROUP BY columnThis is the SQL equivalent of pandas groupby(). And just like in pandas, it is one of the most powerful tools in your analyst toolkit.
Total Sales and Profit by Region
-- Business question: how does each region perform?
query("""
SELECT
region,
COUNT(*) AS total_orders,
ROUND(SUM(sales), 2) AS total_revenue,
ROUND(SUM(profit), 2) AS total_profit
FROM superstore
GROUP BY region
ORDER BY total_profit DESC
""")Output:
| region | total_orders | total_revenue | total_profit |
|---|---|---|---|
| West | 3203 | 725457.82 | 108418.45 |
| East | 2848 | 678781.24 | 91522.78 |
| Central | 2323 | 501239.89 | 39706.36 |
| South | 1620 | 391721.91 | 46749.43 |
Four rows. One per region. Each one summarising thousands of individual transactions. This is what aggregation does — it turns raw data into something a manager can read and act on in seconds.
Grouping by Multiple Columns
You can GROUP BY more than one column to get more granular breakdowns:
-- Revenue and profit broken down by region AND category
query("""
SELECT
region,
category,
ROUND(SUM(sales), 2) AS total_revenue,
ROUND(SUM(profit), 2) AS total_profit
FROM superstore
GROUP BY region, category
ORDER BY region, total_profit DESC
""")This gives you one row per unique region-category combination — 12 rows total (4 regions × 3 categories). A clean, structured view of performance across two dimensions at once.
The GROUP BY Rule — Every Non-Aggregated Column Must Be Listed
This is the rule that trips up every beginner at least once. In a SELECT statement that uses GROUP BY, every column that is not inside an aggregate function must appear in the GROUP BY clause. If you select region and SUM(sales), you must GROUP BY region. If you select region, category, and SUM(sales), you must GROUP BY region, category.
-- This will throw an error — category is selected but not grouped
query("""
SELECT region, category, SUM(sales)
FROM superstore
GROUP BY region -- missing category here
""")-- This is correct
query("""
SELECT region, category, SUM(sales)
FROM superstore
GROUP BY region, category
""")When you get a GROUP BY error, the fix is almost always: add the missing column to the GROUP BY clause.
HAVING — Filtering Aggregated Results
WHERE filters individual rows before aggregation. HAVING filters the aggregated results after GROUP BY runs. This distinction is critical and confuses many beginners.
The simple rule:
- Use WHERE to filter rows — before grouping
- Use HAVING to filter groups — after grouping
Syntax:
SELECT column, AGG_FUNCTION(column)<br>FROM table<br>GROUP BY column<br>HAVING AGG_FUNCTION(column) conditionFilter Groups by Aggregate Value
-- Which regions have total profit above $50,000?
query("""
SELECT
region,
ROUND(SUM(profit), 2) AS total_profit
FROM superstore
GROUP BY region
HAVING SUM(profit) > 50000
ORDER BY total_profit DESC
""")You cannot use WHERE profit > 50000 here because at the time WHERE runs, the profit has not been summed yet. HAVING runs after the sum is calculated — that is why it can filter on it.
WHERE and HAVING Together
WHERE and HAVING can be used in the same query. WHERE filters the raw rows first, then GROUP BY aggregates, then HAVING filters the groups:
-- Among Technology orders only, which sub-categories have total sales above $100,000?
query("""
SELECT
sub_category,
ROUND(SUM(sales), 2) AS total_sales,
ROUND(SUM(profit), 2) AS total_profit
FROM superstore
WHERE category = 'Technology'
GROUP BY sub_category
HAVING SUM(sales) > 100000
ORDER BY total_sales DESC
""")Output:
| sub_category | total_sales | total_profit |
|---|---|---|
| Phones | 330007.05 | 44515.73 |
| Machines | 189238.63 | 3384.76 |
| Copiers | 149528.03 | 55617.82 |
Read the logic out loud: “From all Technology orders, group by sub-category, show only groups with over $100,000 in sales, sorted by sales.” WHERE handles the category filter. HAVING handles the post-aggregation filter. Both in the same query.
Practical Business Queries Using Aggregations
Here are three complete queries that combine everything in this topic to answer real analyst questions.
Which customer segment is most valuable?
query("""
SELECT
segment,
COUNT(*) AS total_orders,
ROUND(SUM(sales), 2) AS total_revenue,
ROUND(AVG(sales), 2) AS avg_order_value,
ROUND(SUM(profit), 2) AS total_profit
FROM superstore
GROUP BY segment
ORDER BY total_revenue DESC
""")Which sub-categories are losing money overall?
query("""
SELECT
sub_category,
ROUND(SUM(profit), 2) AS total_profit,
COUNT(*) AS total_orders
FROM superstore
GROUP BY sub_category
HAVING SUM(profit) < 0
ORDER BY total_profit ASC
""")This query directly answers one of the most common business questions an analyst gets asked: which product lines are unprofitable and how bad is it?
Top performing sub-categories by profit margin
query("""
SELECT
sub_category,
ROUND(SUM(sales), 2) AS total_sales,
ROUND(SUM(profit), 2) AS total_profit,
ROUND(SUM(profit) / SUM(sales) * 100, 1) AS profit_margin_pct
FROM superstore
GROUP BY sub_category
ORDER BY profit_margin_pct DESC
LIMIT 10
""")Notice SUM(profit) / SUM(sales) * 100 — you can do arithmetic directly inside a SELECT using aggregate functions. This calculates the overall profit margin for each sub-category, not an average of individual margins, which gives a more accurate business figure.
WHERE vs HAVING — The Full Picture
This distinction comes up so often it deserves its own summary:
| WHERE | HAVING | |
|---|---|---|
| When it runs | Before GROUP BY | After GROUP BY |
| What it filters | Individual rows | Aggregated groups |
| Can use aggregate functions | No | Yes |
| Example | WHERE region = 'West' | HAVING SUM(sales) > 10000 |
| Common use | Filter raw data before summarising | Keep only groups that meet a threshold |
A useful mental model: WHERE is a row-level gate. HAVING is a group-level gate. Data passes through WHERE first, gets grouped and aggregated, then passes through HAVING.
Common Mistakes with Aggregations
| Mistake | What Happens | Fix |
|---|---|---|
| Using WHERE instead of HAVING | Error or wrong results when filtering on aggregated values | Use HAVING for any condition involving SUM, COUNT, AVG, MIN, MAX |
| Forgetting to GROUP BY all non-aggregated columns | SQL error in strict databases | Add every non-aggregated SELECT column to GROUP BY |
| Not using ROUND on financial figures | Results show many decimal places | Wrap financial aggregations in ROUND(value, 2) |
| Dividing by SUM instead of individual values for margin | Slightly different result than expected | For overall margin use SUM(profit)/SUM(sales), not AVG(profit/sales) |
| Selecting an aggregated alias in HAVING | Error — alias not yet available | Repeat the full expression: HAVING SUM(sales) > 10000, not HAVING total_sales > 10000 |
Practice Exercises
Try these before moving to Topic 4. Each one maps to a real analyst scenario.
- What is the total revenue and total profit for each category? Sort by total profit descending.
- Which ship modes are used most frequently? Show the count of orders per ship mode.
- Find all sub-categories where the average order value is above $300.
- Which states have more than 100 orders? Show state name and order count.
- Among orders in the Consumer segment only, what is the total profit per category?
Summary — What You Can Now Do
- Use COUNT, SUM, AVG, MIN, and MAX to calculate summary statistics across an entire table
- Apply GROUP BY to break aggregations down by one or multiple categories
- Follow the GROUP BY rule — every non-aggregated SELECT column must appear in GROUP BY
- Use HAVING to filter groups after aggregation
- Combine WHERE and HAVING in the same query to filter both rows and groups
- Write business-ready summary queries that answer questions about revenue, profit, and performance by segment
Up next — Topic 4: JOINs
So far every query has touched a single table. In Topic 4 you learn INNER JOIN and LEFT JOIN — how to combine data from two tables into one result. This is where SQL becomes genuinely powerful for real-world databases where customer data, order data, and product data all live separately.