Introduction
Method Three
Instruction
Good job! One advantage of Method 2 as compared to Method 1 is that we can add another dimension to our reports. Take a look:
SELECT
CustomerID,
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
GROUP BY CustomerID;
Result:
| CustomerID | OrdersShipped | OrdersPending |
|---|---|---|
| ALFKI | 6 | 0 |
| ANATR | 4 | 0 |
| ANTON | 7 | 0 |
| ... | ... | ... |
We add the CustomerID column in the SELECT clause, and we also add it later in the GROUP BY clause. This way, we can now see how many orders are shipped or pending for each customer.
Exercise
For each employee, find the total revenue before discount generated by the orders processed for two order groups: DachOrders (orders shipped to Germany, Austria, or Switzerland) and OtherOrders (orders shipped to all other countries).
Show the following columns: EmployeeID, FirstName, LastName, DachOrders, OtherOrders.
Stuck? Here's a hint!
You can calculate the revenue generated by the orders shipped to the three countries using:
SUM(CASE
WHEN ShipCountry IN (N'Switzerland', N'Germany', N'Austria')
THEN Quantity * UnitPrice
ELSE 0
END) AS DachOrders
