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
6. Calculating ratios – step 1
Global vs. specific metrics
Summary

Instruction

Excellent! How can we compute percentages and ratios in our reports? Let's find out.

Suppose we want to know what percentage of all orders have already been shipped. We'll write this query in a few steps. Here's step 1:

SELECT
  COUNT(CASE
    WHEN ShippedDate IS NOT NULL
      THEN OrderID
  END) AS CountShipped,
  COUNT(OrderID) AS CountAll
FROM Orders;

In this step, we simply calculate two separate columns: the numerator and the denominator of our ratio. In this case, the numerator is the number of orders shipped and the denominator is the total number of orders.

Note how we used COUNT() with CASE WHEN to compute one metric (number of orders shipped) and COUNT() to compute the other metric (total number of orders).

Exercise

We want to find the ratio of the revenue from all discounted items to the total revenue from all items. We'll do this in steps, too.

First, show two columns:

  1. DiscountedRevenue – the revenue (after discount) from all discounted line items in all orders.
  2. TotalRevenue – the total revenue (after discount) from line items in all orders.

Stuck? Here's a hint!

You only need the OrderItems table. Use the Discount column to determine whether a given line item was discounted.

You can calculate total revenue as follows:

SUM(UnitPrice * Quantity * (1 - Discount))