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
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
  ship_country,
  COUNT(CASE
    WHEN shipped_date IS NOT NULL
      THEN order_id
  END) AS count_shipped,
  COUNT(order_id) AS count_all,
  ROUND(COUNT(CASE
    WHEN shipped_date IS NOT NULL
      THEN order_id
  END) / COUNT(order_id)::decimal * 100, 2) AS shipped_ratio
FROM orders
GROUP BY ship_country;

We've added the ship_country 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 category_name 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.