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 PostgreSQL

Introduction
Multiple metrics for a single object
Metrics for two groups
Ratios and percentages
7. Calculating ratios – step 2
Global vs. specific metrics
Summary

Instruction

Perfect! Now,we'll add a third column to our query:

SELECT
  COUNT(CASE
    WHEN shipped_date IS NOT NULL
      THEN order_id
  END) AS count_shipped,
  COUNT(order_id) AS count_all,
  COUNT(CASE
    WHEN shipped_date IS NOT NULL
      THEN order_id
  END) / COUNT(order_id)::decimal AS shipped_ratio
FROM orders;

In the third column, we calculate the ratio by dividing the expression from the first column by the expression from the second column. However, both of these values are integers, so we need to force SQL to use floating-point division. To do that, we cast one of the metrics to the decimal data type: COUNT(order_id)::decimal.

Exercise

Add a third column to the template code: discounted_ratio. It should contain the ratio of discounted line items (from column 1) to all line items (from column 2).

Stuck? Here's a hint!

Remember to use ::decimal for floating-point division.