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
Ratios and percentages
10. Calculating percentages in groups – explanation
Global vs. specific metrics
Summary

Instruction

Very well done! One more thing we can do is show ratios/percentages in groups. Take a look:

SELECT
  ShipCountry,
  COUNT(CASE
    WHEN ShippedDate IS NOT NULL
      THEN OrderID
  END) AS CountShipped,
  COUNT(OrderID) AS CountAll,
  ROUND(COUNT(CASE
    WHEN ShippedDate IS NOT NULL
      THEN OrderID
  END) / CAST(COUNT(OrderID) AS float) * 100, 2) AS ShippedRatio
FROM Orders
GROUP BY ShipCountry;

We've added the ShipCountry column to the GROUP BY and SELECT clauses. This way, we'll see the percentage of shipped orders for each country separately.

Exercise

Modify the template query so that it shows all data grouped by product category. Add CategoryName as the first column in the SELECT clause.

Stuck? Here's a hint!

Join the Products and Categories tables. Group by category ID and category name.