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
7. Custom grouping
Custom counting of business objects
Summary

Instruction

Very well done!

Before we introduce free shipping to the USA and Canada, we'd like to know how many orders are sent to these countries and how many are sent to other places. Take a look:

SELECT 
  CASE
    WHEN ShipCountry = N'USA' OR ShipCountry = N'Canada' THEN 0.0
    ELSE 10.0
  END AS ShippingCost,
  COUNT(*) AS OrderCount
FROM Orders
GROUP BY
  CASE
    WHEN ShipCountry = N'USA' OR ShipCountry = N'Canada' THEN 0.0
    ELSE 10.0
  END;

In the SELECT clause, we used the CASE WHEN construction you've seen before. However, you can also see that the same CASE WHEN construction appears in the GROUP BY clause, only without the ShippingCost alias. Even though we already defined it in the SELECT clause and gave it an alias (ShippingCost), MS SQL Server doesn't allow referring to an alias in the GROUP BY clause (i.e., we can't write GROUP BY ShippingCost). That's why we had to repeat the whole construction.

Because we now group by the CASE WHEN construction, we could add a COUNT(*) column in the SELECT clause. The query will show:

ShippingCost OrderCount
10.0 678
0.0 152

Remember: The CASE WHEN construction should be the same in both the GROUP BY and SELECT clauses!

Exercise

Create a report that shows the number of products supplied from a specific continent. Display two columns: SupplierContinent and ProductCount. The SupplierContinent column should have the following values:

  • N'North America' for products supplied from N'USA' and N'Canada'.
  • N'Asia' for products from N'Japan' and N'Singapore'.
  • N'Other' for other countries.

Stuck? Here's a hint!

Use the following CASE WHEN statement in the SELECT clause:

CASE
  WHEN ... THEN N'North America'
  WHEN ... THEN N'Asia'
  ELSE N'Other'
END AS SupplierContinent

Repeat the same CASE WHEN structure in the GROUP BY clause, but remove the alias.