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

Introduction
Custom classifications of business objects
Custom grouping of business objects
Custom counting of business objects
11. GROUP BY with CASE WHEN
Summary

Instruction

Good job! We can also use the same technique to split the results into multiple groups:

SELECT 
  ship_country,
  COUNT(CASE
    WHEN freight < 40.0 THEN order_id
  END) AS low_freight,
  COUNT(CASE
    WHEN freight >= 40.0 AND freight < 80.0 THEN order_id
  END) AS avg_freight,
  COUNT(CASE
    WHEN freight >= 80.0 THEN order_id
  END) AS high_freight
FROM orders
GROUP BY ship_country;

Result:

ship_country low_freight avg_freight high_freight
Finland 16 3 3
USA 53 22 47
Italy 20 6 2
... ... ... ...

By combining COUNT(CASE WHEN...) with GROUP BY, we've created a more advanced report that shows the number of orders with low, average, and high freight in each country.

Exercise

We need a report that will show the number of products with high and low availability in all product categories. Show three columns: category_name, high_availability (count the products with more than 30 units in stock) and low_availability (count the products with 30 or fewer units in stock).

Stuck? Here's a hint!

JOIN the products and categories tables. Group by category_id and category_name.