Instruction

Very well done! Often, you want to create reports where you count the number of objects in two different groups, based on your classification. Look at the following query:

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;

The report above shows each CustomerID alongside the number of orders already shipped to that customer and the number of orders that have not yet been shipped yet. Note that we use COUNT() with CASE WHEN twice to count the number of objects in the two different groups. Both metrics however, refer to the same business object (the same customer).

Exercise

For each category, show the number of products in stock (i.e., products where UnitsInStock > 0) and the number of products not in stock. The report should contain three columns:

CategoryName

ProductsInStock

ProductsNotInStock

Stuck? Here's a hint!

Use the CASE WHEN construction to check if a given product has UnitsInStock > 0.

Need assistance?

Our website uses cookies. By using this website, you agree to their use in accordance with the browser settings.You can modify your browser settings on your own. For more information see our Privacy Policy.