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
Providing detailed information and counting objects
Calculating metrics for multiple business objects
6. Total order value
Understanding the difference between various count metrics
Summary

Instruction

Good job! In sales reports, we frequently need to calculate the total sum paid for an order. Let's take a look at how we can do that:

SELECT
  SUM(UnitPrice * Quantity) AS TotalPrice
FROM Orders O
JOIN OrderItems OI
  ON O.OrderID = OI.OrderID
WHERE O.OrderID = 10250;

We want to find the total price (before discount) for the order with ID of 10250. As you can see, we use SUM(UnitPrice * Quantity). Like COUNT(), SUM() is frequently used in business reports.

Exercise

The template code shows the query from the explanation. The Northwind store offers its customers discounts for some products. The discount for each item is stored in the Discount column of the OrderItem table. (For example, a 0.20 discount means that the customer pays 1 - 0.2 = 0.8 of the original price.) Your task is to add a second column named TotalPriceAfterDiscount.

Stuck? Here's a hint!

The new column should use the following formula:

UnitPrice * Quantity * (1 - Discount)