Back-To-School Deals - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Method One
Method Two
Method Three
8. Method 3 – explanation
Summary

Instruction

Finally, let's take a look at Method 3. It's a bit more complicated. Method 3 is typically the best solution for more complex reports, such as ones with percentages or multi-level aggregation.

For each employee, we want to show the percentage of orders that have already been shipped that are going to Germany and the USA. This is what we want to see:

FirstName LastName GermanyPerc USAPerc
Nancy Davolio 100 95.24
Andrew Fuller 92.86 100
Janet Leverling 100 100
... ... ... ...

And here's the query:

WITH GermanyOrders AS (
  SELECT
    EmployeeID,
    COUNT(CASE
      WHEN ShippedDate IS NOT NULL
        THEN OrderID
    END) AS CountShipped,
    COUNT(OrderID) AS CountAll
  FROM Orders O
  WHERE O.ShipCountry = N'Germany'
  GROUP BY EmployeeID
),
USAOrders AS (
  SELECT
    EmployeeID,
    COUNT(CASE
      WHEN ShippedDate IS NOT NULL
        THEN OrderID
    END) AS CountShipped,
    COUNT(OrderID) AS CountAll
  FROM Orders O
  WHERE O.ShipCountry = N'USA'
  GROUP BY EmployeeID
)
SELECT
  E.FirstName,
  E.LastName,
  ROUND(GeOr.CountShipped / CAST(GeOr.CountAll AS float) * 100, 2) AS GermanyPerc,
  ROUND(UsOr.CountShipped / CAST(UsOr.CountAll AS float) * 100, 2) AS USAPerc
FROM GermanyOrders GeOr
FULL OUTER JOIN USAOrders UsOr
  ON GeOr.EmployeeID = UsOr.EmployeeID
JOIN Employees E
  ON GeOr.EmployeeID = E.EmployeeID
  OR UsOr.EmployeeID = E.EmployeeID;

The general idea is to use separate CTEs to calculate metrics for each group, and then combine the results in the outer query.

Here, we have separate CTEs for German and American (USA) orders. In each CTE, we group orders by the EmployeeID column and calculate the numerator and denominator needed to find the percentages. In the outer query, we join both CTEs on the EmployeeID column and perform division to get the results.

Note the use of a FULL OUTER JOIN. A simple join only shows rows with matching column values in both tables. This means that employees who only shipped orders to one of the two countries wouldn't be shown at all. A FULL OUTER JOIN solves this problem.

Exercise

For each ShipCountry, we want to see the percentage of revenue for all orders before discount that has been generated by the employees with the IDs 1 and 2. Show three columns:

  1. The country to which an order is being shipped (ShipCountry).
  2. PercentageEmployee1 – the percentage of pre-discount revenue generated by the employee with ID 1.
  3. PercentageEmployee2 – the percentage of pre-discount revenue generated by the employee with ID 2.

Round the percentages to two decimal places.

Stuck? Here's a hint!

Create two CTEs, RevenueEmployee1 and RevenueEmployee2. In each of them, return the following columns: ShipCountry, EmployeeRevenue, TotalRevenue. In the outer query, join the results on the ShipCountry column.