Lære Subqueries for Complex Analysis | Advanced SQL for Data Analysis

Subqueries are a powerful feature in SQL that allow you to embed one query within another. This technique opens up new possibilities for complex analysis, such as comparing individual values to calculated aggregates or filtering based on dynamic criteria.

Consider a situation where you want to find customers whose total purchases are above the average for all customers. Instead of calculating the average separately and plugging it into your query, you can use a subquery to handle both steps in one statement. This approach keeps your SQL concise, readable, and adaptable to changes in the data.


              12345678910111213
            
-- Find customers with total purchases above the average
SELECT c.customer_id, c.first_name, c.last_name, SUM(s.total_amount) AS total_purchases
FROM customers c
JOIN sales s ON c.customer_id = s.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
HAVING SUM(s.total_amount) > (
    SELECT AVG(total_purchases)
    FROM (
        SELECT SUM(total_amount) AS total_purchases
        FROM sales
        GROUP BY customer_id
    ) AS customer_totals
);

You can use subqueries in several parts of an SQL statement. The most common locations are the SELECT clause (to calculate values for each row), the FROM clause (to define a derived table or virtual result set), and the WHERE clause (to filter results based on another query). Subqueries let you perform calculations or filtering that depend on aggregated, grouped, or otherwise intermediate results, all within a single query. This makes your analysis more flexible and can eliminate the need for temporary tables or manual calculations.


              123456789
            
-- Calculate total sales per product using a subquery in the FROM clause
SELECT p.product_id, p.product_name, sales_totals.total_sales
FROM products p
JOIN (
    SELECT product_id, SUM(total_amount) AS total_sales
    FROM sales
    GROUP BY product_id
) AS sales_totals
ON p.product_id = sales_totals.product_id;

1. Where can subqueries be used in SQL?

2. What is the main benefit of using a subquery?

3. Fill in the blanks to complete the query. Find all products whose total sales are above the average total sales for all products.

Alt var klart?

Takk for tilbakemeldingene dine!

Seksjon 4. 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

Sveip for å vise menyen