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.
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.
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
