Introduction

Questions about "group by in group by and average" usually come from mixing two different levels of aggregation. You may want to aggregate rows once, then compute an average over those grouped results. SQL can do that, but not in a single flat GROUP BY clause.

The usual solution is a subquery or common table expression. First produce the grouped totals you care about, then run a second query that averages those totals.

Think in Terms of Data Grain

Before writing SQL, ask what one row means at each step.

Suppose you have an orders table:

1CREATE TABLE orders (
2    order_id INT,
3    customer_id INT,
4    order_date DATE,
5    amount DECIMAL(10, 2)
6);

If you write:

SELECT customer_id, AVG(amount)
FROM orders
GROUP BY customer_id;

you get the average order amount per customer. That is a single level of grouping, and it is valid.

But what if you want the average daily total per customer? That is different. You must first compute each customer's total for each day, then average those daily totals.

Use a Subquery or CTE for Nested Aggregation

Here is the correct pattern with a CTE:

1WITH daily_totals AS (
2    SELECT
3        customer_id,
4        order_date,
5        SUM(amount) AS daily_total
6    FROM orders
7    GROUP BY customer_id, order_date
8)
9SELECT
10    customer_id,
11    AVG(daily_total) AS avg_daily_total
12FROM daily_totals
13GROUP BY customer_id;

The first query changes the grain from "one row per order" to "one row per customer per day." The outer query then changes the grain again to "one row per customer" and averages the grouped totals.

That is the core idea behind "group by inside group by."

Why AVG(SUM(...)) Usually Fails

Many people try something like this:

SELECT customer_id, AVG(SUM(amount))
FROM orders
GROUP BY customer_id, order_date;

That is not valid in normal SQL because aggregate functions such as SUM and AVG cannot usually be nested at the same query level. SQL needs the first aggregation to finish before the second one begins.

A subquery gives the database that separation:

1SELECT AVG(daily_total) AS overall_avg_daily_sales
2FROM (
3    SELECT
4        order_date,
5        SUM(amount) AS daily_total
6    FROM orders
7    GROUP BY order_date
8) AS grouped_days;

This version asks a slightly different question: the average of total daily sales across the whole business, not per customer.

Another Example: Average of Category Totals

Imagine a sales table with region, product_category, and revenue. If you want average category revenue per region, you can do the same two-step pattern:

1WITH category_totals AS (
2    SELECT
3        region,
4        product_category,
5        SUM(revenue) AS category_revenue
6    FROM sales
7    GROUP BY region, product_category
8)
9SELECT
10    region,
11    AVG(category_revenue) AS avg_category_revenue
12FROM category_totals
13GROUP BY region;

Again, the first step defines category-level totals, and the second step averages them within each region.

WHERE Versus HAVING

When building these queries, keep filtering stages straight:

• 'WHERE filters raw rows before grouping'
• 'HAVING filters groups after aggregation'

For example, if you want to average only daily totals above 100, the filter belongs after the first grouping:

1WITH daily_totals AS (
2    SELECT
3        customer_id,
4        order_date,
5        SUM(amount) AS daily_total
6    FROM orders
7    GROUP BY customer_id, order_date
8    HAVING SUM(amount) > 100
9)
10SELECT
11    customer_id,
12    AVG(daily_total)
13FROM daily_totals
14GROUP BY customer_id;

Common Pitfalls

• Forgetting that aggregation happens at a specific grain. The wrong grouping columns change the question you are answering.
• Trying to nest AVG(SUM(...)) in a single query block.
• Using AVG(amount) when the real requirement is average of grouped totals.
• Filtering grouped values with WHERE instead of HAVING.
• Ignoring NULL handling. AVG skips NULL, which can change your results if the grouped expression sometimes returns NULL.

Summary

• Use one query level for each aggregation level.
• First group the detailed rows, then average the grouped results in an outer query.
• Think about row grain before you think about syntax.
• Use HAVING when you need to filter groups after aggregation.
• If your SQL feels like it needs GROUP BY inside GROUP BY, you probably need a subquery or CTE.