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

Instruction

Let's get started! The first method we're going to show allows you to put groups into separate rows.

Suppose we want to show the number of orders shipped to North America and the number of orders shipped to other places in separate rows, like this:

ShippingContinent OrderCount
North America 180
Other 650

We can use the following query:

WITH OrdersByGroup AS (
  SELECT 
    OrderID,
    CASE
      WHEN ShipCountry IN (N'USA', N'Canada', N'Mexico')
        THEN N'North America'
      ELSE N'Other' 
    END AS ShippingContinent
  FROM Orders
)
SELECT
  ShippingContinent,
  COUNT(OrderID) AS OrderCount
FROM OrdersByGroup
GROUP BY ShippingContinent;

Inside the inner query, we select the OrderID and use the CASE WHEN construction to classify orders based on the ShipCountry column. The classification result is stored in a column named ShippingContinent, which is either N'North America' or N'Other'. You can define as many values as you want; you don't need to limit yourself to two.

In the outer query, we group all rows from the inner query by the ShippingContinent column and use the COUNT(OrderID) function to count matching orders. As a result, each group is shown in a separate row.

Exercise

Count the orders processed by employees from the N'WA' region and by all other employees. Show two columns: EmployeeRegion (either N'WA' or N'Not WA'), and OrderCount.

Stuck? Here's a hint!

In the inner query, you'll have to join the Employees and Orders tables.