Rainbow Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Method One
Method Two
5. Method 2 – explanation
Method Three
Summary

Instruction

Very well done! Let's move on to method two. This method shows different groups in separate columns.

Let's say we want to count the number of orders already shipped and the number of orders not yet shipped:

OrdersShipped OrdersPending
809 21

Take a look at the query:

SELECT
  COUNT(CASE
    WHEN ShippedDate IS NOT NULL
      THEN OrderID
  END) AS OrdersShipped,
  COUNT(CASE
    WHEN ShippedDate IS NULL
      THEN OrderID
  END) AS OrdersPending
FROM Orders;

In this technique, we use multiple COUNT(CASE WHEN...) or SUM(CASE WHEN...) statements in the query. Each statement is used to show the metric for a single group in a new column. We also need to name each column using the X AS Alias construction.

In Method 1, groups were represented as rows and created with a single CASE WHEN statement. Here, they are represented as columns and created with separate aggregate function invocations. This is why Method 2 is typically a good choice for a small number of groups.

Exercise

Count the number of vegetarian and non-vegetarian products. Show two columns: NonVegetarianCount and VegetarianCount.

Hint: Non-vegetarian products have a CategoryID of 6 or 8.

Stuck? Here's a hint!

You can count non-vegetarian products in the following way:

COUNT(CASE
  WHEN CategoryID IN (6, 8)
    THEN ProductID
END)