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 name_of_cte AS (
      SELECT
        ...,
        CASE
          WHEN ...
          ELSE ...
        END AS group_label
      FROM table_name
    )
    SELECT
      group_label,
      COUNT(...) 
    FROM name_of_cte
    GROUP BY group_label;
    
  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
      group_column,
      COUNT(CASE
        WHEN ... THEN ...
      END) AS group_one,
      COUNT(CASE
        WHEN ... THEN ...
      END) AS group_two
    FROM table_name
    GROUP BY group_column;
    
  3. In Method 3, each group is calculated in a separate CTE. Groups are then combined in the outer query.
    WITH group_one AS (...), 
    group_two AS (...)
    SELECT
      group_one.column_name,
      group_two.column_name
    FROM group_one
    FULL OUTER JOIN group_two
      ON ...;
    

How about a short quiz?

Exercise

Click Next exercise to continue.