Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Method One
Method Two
Method Three
Summary
10. Summary

Instruction

That's all we wanted to show you in this part! Before we review, let's do a quick summary.

We've talked about three methods to compare different business groups in a single query.

  1. In Method 1, we use a CASE WHEN construction in a CTE to label business objects. In the outer query, we group the rows by these labels. Groups are shown in separate rows.
    WITH NameOfCTE AS (
      SELECT
        ...,
        CASE
          WHEN ...
          ELSE ...
        END AS GroupLabel
      FROM TableName
    )
    SELECT
      GroupLabel,
      COUNT(...) 
    FROM NameOfCTE
    GROUP BY GroupLabel;
    
  2. In Method 2, we use multiple COUNT/SUM(CASE WHEN...) statements to create groups. The groups are shown in separate columns. We can also add more groups.
    SELECT
      GroupColumn,
      COUNT(CASE
        WHEN ... THEN ...
      END) AS GroupOne,
      COUNT(CASE
        WHEN ... THEN ...
      END) AS GroupTwo
    FROM TableName
    GROUP BY GroupColumn;
    
  3. In Method 3, each group is calculated in a separate CTE. Groups are then combined in the outer query.
    WITH GroupOne AS (...), 
    GroupTwo AS (...)
    SELECT
      GroupOne.ColumnName,
      GroupTwo.ColumnName
    FROM GroupOne
    FULL OUTER JOIN GroupTwo
      ON ...;
    

How about a short quiz?

Exercise

Click Next exercise to continue.