Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Creating Basic SQL Reports in SQL Server

Introduction
Multiple metrics for a single object
Metrics for two groups
4. Custom metrics – explanation
Ratios and percentages
Global vs. specific metrics
Summary

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.