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:
Take a look at the query:
WHEN ShippedDate IS NOT NULL
END) AS OrdersShipped,
WHEN ShippedDate IS NULL
END) AS OrdersPending
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.