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
13. CASE WHEN with SUM – exercise
Summary

Instruction

Good job! Let's write one more query with SUM(), this time from scratch!

Exercise

There have been a lot of orders shipped to France. Of these, how many order items were sold at full price and how many were discounted? Show two columns with the respective counts: FullPrice and DiscountedPrice.

Stuck? Here's a hint!

Use the Discount column from the OrderItems table.

Here's an example of using SUM() (from the previous exercise):

SELECT 
  SUM(CASE
    WHEN Region = N'WA' THEN 1
  END) AS OrdersWAEmployees,
  SUM(CASE
    WHEN Region != N'WA' THEN 1
  END) AS OrdersNotWAEmployees
FROM Employees E
JOIN Orders O
  ON E.EmployeeID = O.EmployeeID;