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

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