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
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 
  ShipCountry,
  COUNT(CASE
    WHEN Freight < 40.0 THEN OrderID
  END) AS LowFreight,
  COUNT(CASE
    WHEN Freight >= 40.0 AND Freight < 80.0 THEN OrderID
  END) AS AvgFreight,
  COUNT(CASE
    WHEN Freight >= 80.0 THEN OrderID
  END) AS HighFreight
FROM Orders
GROUP BY ShipCountry;

Result:

ShipCountry LowFreight AvgFreight HighFreight
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: CategoryName, HighAvailability (count the products with more than 30 units in stock) and LowAvailability (count the products with 30 or fewer units in stock).

Stuck? Here's a hint!

JOIN tables Products and Categories tables. Group by CategoryID and CategoryName.