Impara Aggregating Data with Nested Subqueries in FROM | Nested Subqueries in the FROM section

Scorri per mostrare il menu

When you need to summarize data at more than one level—such as calculating sales totals by category and then finding the average sales per category—you can use nested subqueries in the FROM clause. These subqueries, often called derived tables, allow you to perform an initial aggregation or transformation of your data, and then use the resulting summarized data as the source for further aggregation in the outer query. This approach is especially useful when you want to build on intermediate results or need to perform calculations that depend on previously grouped data.


              12345678910111213141516
            
-- Aggregate sales by category in a subquery, then calculate average sales per category in the outer query
SELECT
    category_sales.category_name,
    category_sales.total_sales,
    AVG(category_sales.total_sales) OVER () AS avg_sales_per_category
FROM (
    SELECT
        pc.category_name,
        SUM(s.total_amount) AS total_sales
    FROM
        sales s
        JOIN products p ON s.product_id = p.product_id
        JOIN product_categories pc ON p.category_id = pc.category_id
    GROUP BY
        pc.category_name
) AS category_sales;

Multi-level aggregation is necessary in scenarios where you need to answer questions that require more than one step of summarization. For instance, you might want to know not just the total sales per product category, but also how those totals compare to the average across all categories. Another example is analyzing customer purchase behavior by grouping customers into demographic segments, aggregating their purchases, and then comparing those aggregates across segments. Using nested subqueries in the FROM clause helps you break complex problems into manageable steps, making your queries easier to write, understand, and maintain.


              123456789101112131415
            
-- Aggregate customer purchases by demographic group (e.g., gender), then compare total purchases
SELECT
    customer_groups.gender,
    customer_groups.total_purchases,
    AVG(customer_groups.total_purchases) OVER () AS avg_purchases_by_gender
FROM (
    SELECT
        c.gender,
        SUM(s.total_amount) AS total_purchases
    FROM
        sales s
        JOIN customers c ON s.customer_id = c.customer_id
    GROUP BY
        c.gender
) AS customer_groups;

1. What is one advantage of using multi-level aggregation with subqueries in the FROM clause?

2. When structuring a nested aggregation, where should you perform the initial aggregation (such as SUM or COUNT)?

3. You want to aggregate total sales by category, then calculate the average of those totals. Where should the GROUP BY for `category` be placed?

Tutto è chiaro?

Grazie per i tuoi commenti!

Sezione 2. Capitolo 3

Chieda ad AI

Chieda pure quello che desidera o provi una delle domande suggerite per iniziare la nostra conversazione

Sezione 2. Capitolo 3

Aggregating Data with Nested Subqueries in FROM

1. What is one advantage of using multi-level aggregation with subqueries in the FROM clause?

2. When structuring a nested aggregation, where should you perform the initial aggregation (such as SUM or COUNT)?

3. You want to aggregate total sales by category, then calculate the average of those totals. Where should the GROUP BY for category be placed?

3. You want to aggregate total sales by category, then calculate the average of those totals. Where should the GROUP BY for `category` be placed?