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
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
  customer_id,
  COUNT(CASE
    WHEN shipped_date IS NOT NULL
      THEN order_id
  END) AS orders_shipped,
  COUNT(CASE
    WHEN shipped_date IS NULL
      THEN order_id
  END) AS orders_pending
FROM orders
GROUP BY customer_id;

The report above shows each customer_id alongside the number of orders already shipped to that customer and the number of orders that have not yet been shipped. 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 units_in_stock > 0) and the number of products not in stock. The report should contain three columns:

  • category_name
  • products_in_stock
  • products_not_in_stock

Stuck? Here's a hint!

Use the CASE WHEN construction to check if a given product has units_in_stock > 0.