Lære CTEs with Aggregate Functions | Advanced CTE Applications and Performance

Sveip for å vise menyen

Understanding how to use aggregate functions within Common Table Expressions (CTEs) allows you to perform advanced analytics and reporting with clarity and efficiency. Aggregate functions such as SUM, COUNT, and AVG are powerful tools for summarizing large datasets, and when combined with CTEs, they let you break down complex queries into logical, manageable steps. This approach is especially useful when you need to analyze sales, customer activity, or inventory trends in a structured and readable way.


              12345678910111213141516171819202122
            
-- Calculate total sales per customer using SUM within a CTE
WITH customer_sales AS (
    SELECT
        c.customer_id,
        c.first_name,
        c.last_name,
        SUM(st.total_amount) AS total_sales
    FROM
        customers c
        JOIN sales_transactions st ON c.customer_id = st.customer_id
    GROUP BY
        c.customer_id, c.first_name, c.last_name
)
SELECT
    customer_id,
    first_name,
    last_name,
    total_sales
FROM
    customer_sales
ORDER BY
    total_sales DESC;

Using a CTE for aggregation, as in the previous example, helps you organize your SQL code by separating each logical step. Instead of writing a long, nested query, you define a CTE to handle the aggregation, then reference the summarized data in your final SELECT statement. This makes your queries easier to read, debug, and maintain, especially when working with multiple levels of grouping or filtering.


              12345678910111213141516171819202122232425
            
-- Find departments (product categories) with above-average sales using a CTE
WITH category_sales AS (
    SELECT
        p.category,
        SUM(st.total_amount) AS total_category_sales
    FROM
        products p
        JOIN sales_transactions st ON p.product_id = st.product_id
    GROUP BY
        p.category
),
average_sales AS (
    SELECT AVG(total_category_sales) AS avg_sales
    FROM category_sales
)
SELECT
    cs.category,
    cs.total_category_sales
FROM
    category_sales cs,
    average_sales a
WHERE
    cs.total_category_sales > a.avg_sales
ORDER BY
    cs.total_category_sales DESC;

1. What is the benefit of using a CTE with aggregate functions?

2. Fill in the blanks to complete a CTE that calculates the average sales per product.

3. Which aggregate function would you use to count the number of orders per customer?

Alt var klart?

Takk for tilbakemeldingene dine!

Seksjon 3. Kapittel 1

Spør AI

Spør om hva du vil, eller prøv ett av de foreslåtte spørsmålene for å starte chatten vår

Seksjon 3. Kapittel 1