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:

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.