Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Basic multi-level aggregation
Multi-level aggregation in groups
Multi-level aggregation with custom classification
Three or more aggregation levels
Summary
15. Summary

Instruction

Good job! Now, let's wrap things up. First, here's a summary of what we've covered in this part:

  1. Multi-level aggregations require common table expressions (CTEs). The most basic CTE looks like this:
    WITH InnerQueryName AS (
      SELECT
        Function(Column1) AS AggColumn1
      FROM ...
    )
    SELECT
      Function(AggColumn1)
    FROM InnerQueryName;
    
  2. We can add a GROUP BY clause in the outer query to show multi-level aggregation for multiple business objects:
    WITH InnerQueryName AS (
      SELECT
        Function(Column1) AS AggColumn1,
        Column2
      FROM ...
    )
    SELECT Function(AggColumn1)
    FROM InnerQueryName
    GROUP BY Column2;
    
  3. We can also use a CASE WHEN construction to introduce our own classifications into multi-level aggregations:
    WITH InnerQueryName AS (
      SELECT
        Function(Column1) AS AggColumn1, 
        CASE
          WHEN ...
        END AS Column2
      FROM ...
    )
    SELECT Function(AggColumn1)
    FROM InnerQueryName
    GROUP BY Column2;
    
  4. Finally, we can use more than one CTE in a query:
    WITH Cte1 AS (...),
    Cte2 AS (...),
    ...,
    CteN AS (...)
    SELECT
      ...
    

Let's solve a few problems before we go...

Exercise

Click Next exercise to continue.