Aggregating Data with Subqueries, HAVING, and CASE in SQL

1. Why Aggregation with Subqueries Matters

As SQL queries become more complex, you often need to:

• Aggregate data (using COUNT, SUM, AVG)
• Filter aggregated results
• Compare group-level values to overall totals
• Categorize results using conditional logic

This requires combining:

• GROUP BY
• Subqueries
• HAVING
• CASE

These tools allow layered, structured aggregation.

2. WHERE vs HAVING

Understanding the difference is critical.

2.1 WHERE

• Filters raw rows.
• Applied before aggregation.
• Cannot filter aggregated results.

Example:

SELECT warehouse_id
FROM orders
WHERE order_date >= '2024-01-01';

SELECT warehouse_id
FROM orders
WHERE order_date >= '2024-01-01';

2.2 HAVING

• Filters aggregated results.
• Applied after GROUP BY.
• Can filter using aggregate functions.

Example:

SELECT warehouse_id, COUNT(*) AS order_count
FROM orders
GROUP BY warehouse_id
HAVING COUNT(*) > 10;

SELECT warehouse_id, COUNT(*) AS order_count
FROM orders
GROUP BY warehouse_id
HAVING COUNT(*) > 10;

Key Rule:

WHERE filters rows.
HAVING filters grouped results.

3. Problem Scenario: Warehouse Order Fulfillment Percentage

We have two tables:

Orders Table

• order_id
• customer_id
• warehouse_id
• order_date
• ship_date

Warehouse Table

• warehouse_id
• warehouse_alias
• maximum_capacity
• total_employees
• state

Goal:

Determine:

• How many orders each warehouse fulfills
• What percentage of total orders that represents
• Categorize warehouses by fulfillment share

4. Aliasing Tables

Aliasing simplifies long queries.

Example:

FROM warehouse w
LEFT JOIN orders o
ON w.warehouse_id = o.warehouse_id

FROM warehouse w
LEFT JOIN orders o
ON w.warehouse_id = o.warehouse_id

Now:

• w refers to warehouse
• o refers to orders

This improves readability.

5. Building the Aggregation Logic

Step 1: Join Tables

We use LEFT JOIN because:

• We want all warehouses.
• Even those with no matching orders.

FROM warehouse w
LEFT JOIN orders o
ON w.warehouse_id = o.warehouse_id

FROM warehouse w
LEFT JOIN orders o
ON w.warehouse_id = o.warehouse_id

Step 2: Group Results

GROUP BY w.warehouse_id, w.warehouse_alias, w.state

GROUP BY w.warehouse_id, w.warehouse_alias, w.state

Grouping ensures:

• One row per warehouse.

Step 3: Count Orders per Warehouse

COUNT(o.order_id) AS warehouse_orders

COUNT(o.order_id) AS warehouse_orders

This gives total fulfilled orders per warehouse.

6. Subquery for Total Orders

We need total company-wide orders.

Subquery:

(SELECT COUNT(*) FROM orders) AS total_orders

(SELECT COUNT(*) FROM orders) AS total_orders

This is a scalar subquery:

• Returns one value.
• Used inside SELECT.

It executes first.
Its value is reused for each outer row.

7. Calculating Percentage

To compute fulfillment percentage:

COUNT(o.order_id) / 
(SELECT COUNT(*) FROM orders)

COUNT(o.order_id) / 
(SELECT COUNT(*) FROM orders)

This yields a fraction.

Multiply by 100 if percentage format is desired.

8. Using CASE for Categorization

CASE allows conditional classification.

Example:

CASE
    WHEN COUNT(o.order_id) / 
         (SELECT COUNT(*) FROM orders) <= 0.2
    THEN 'Fulfilled 0-20% of Orders'    WHEN COUNT(o.order_id) / 
         (SELECT COUNT(*) FROM orders) <= 0.6
    THEN 'Fulfilled 21-60% of Orders'    ELSE 'Fulfilled more than 60% of Orders'
END AS fulfillment_summary

CASE
    WHEN COUNT(o.order_id) / 
         (SELECT COUNT(*) FROM orders) <= 0.2
    THEN 'Fulfilled 0-20% of Orders'    WHEN COUNT(o.order_id) / 
         (SELECT COUNT(*) FROM orders) <= 0.6
    THEN 'Fulfilled 21-60% of Orders'    ELSE 'Fulfilled more than 60% of Orders'
END AS fulfillment_summary

CASE structure:

CASE
    WHEN condition THEN result
    WHEN condition THEN result
    ELSE result
END

CASE
    WHEN condition THEN result
    WHEN condition THEN result
    ELSE result
END

9. Filtering with HAVING

We want to exclude warehouses under construction.

These warehouses have:

• No orders.
• COUNT(o.order_id) = 0.

So we use:

HAVING COUNT(o.order_id) > 0

HAVING COUNT(o.order_id) > 0

This filters after grouping.

10. Full Conceptual Query Structure

SELECT
    w.warehouse_id,
    CONCAT(w.state, ' - ', w.warehouse_alias) AS warehouse_name,
    COUNT(o.order_id) AS warehouse_orders,
    (SELECT COUNT(*) FROM orders) AS total_orders,
    CASE
        WHEN COUNT(o.order_id) / 
             (SELECT COUNT(*) FROM orders) <= 0.2
        THEN 'Fulfilled 0-20% of Orders'
        WHEN COUNT(o.order_id) / 
             (SELECT COUNT(*) FROM orders) <= 0.6
        THEN 'Fulfilled 21-60% of Orders'
        ELSE 'Fulfilled more than 60% of Orders'
    END AS fulfillment_summary
FROM warehouse w
LEFT JOIN orders o
ON w.warehouse_id = o.warehouse_id
GROUP BY w.warehouse_id, w.warehouse_alias, w.state
HAVING COUNT(o.order_id) > 0;

SELECT
    w.warehouse_id,
    CONCAT(w.state, ' - ', w.warehouse_alias) AS warehouse_name,
    COUNT(o.order_id) AS warehouse_orders,
    (SELECT COUNT(*) FROM orders) AS total_orders,
    CASE
        WHEN COUNT(o.order_id) / 
             (SELECT COUNT(*) FROM orders) <= 0.2
        THEN 'Fulfilled 0-20% of Orders'
        WHEN COUNT(o.order_id) / 
             (SELECT COUNT(*) FROM orders) <= 0.6
        THEN 'Fulfilled 21-60% of Orders'
        ELSE 'Fulfilled more than 60% of Orders'
    END AS fulfillment_summary
FROM warehouse w
LEFT JOIN orders o
ON w.warehouse_id = o.warehouse_id
GROUP BY w.warehouse_id, w.warehouse_alias, w.state
HAVING COUNT(o.order_id) > 0;

11. Execution Order

SQL processes this query logically in this order:

1. FROM
2. JOIN
3. WHERE (if present)
4. GROUP BY
5. HAVING
6. SELECT
7. ORDER BY (if present)

Subqueries execute when encountered in SELECT or HAVING.

12. Key Concepts Demonstrated

This query integrates:

• JOIN
• GROUP BY
• Aggregate functions
• Scalar subquery
• CASE conditional logic
• HAVING filter
• Aliasing

This is an advanced SQL aggregation pattern.

13. Why This Is Powerful

This structure allows:

• Comparison of group totals to overall totals
• Dynamic percentage calculations
• Automated categorization
• Filtering aggregated outputs
• Complex performance analysis

Such patterns are common in:

• Sales reporting
• Performance dashboards
• Operations analysis
• Market share studies

14. Summary

To aggregate data with subqueries:

• Use GROUP BY to aggregate.
• Use HAVING to filter aggregated results.
• Use subqueries to calculate global metrics.
• Use CASE to categorize results.
• Use aliases to simplify complex logic.

Subqueries combined with HAVING and CASE enable layered, scalable SQL analytics.