Summer 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
  customer_id,
  COUNT(CASE
    WHEN shipped_date IS NOT NULL
      THEN order_id
  END) AS orders_shipped,
  COUNT(CASE
    WHEN shipped_date IS NULL
      THEN order_id
  END) AS orders_pending
FROM orders
GROUP BY customer_id;

Result:

customer_id orders_shipped orders_pending
ALFKI 6 0
ANATR 4 0
ANTON 7 0
... ... ...

We add the customer_id 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: dach_orders (orders shipped to Germany, Austria, or Switzerland) and other_orders (orders shipped to all other countries).

Show the following columns: employee_id, first_name, last_name, dach_orders, other_orders.

Stuck? Here's a hint!

You can calculate the revenue generated by the orders shipped to the three countries using:

SUM(CASE
  WHEN ship_country IN ('Switzerland', 'Germany', 'Austria')
    THEN quantity * unit_price
  ELSE 0
END) AS dach_orders