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 PostgreSQL

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 amount paid for an order. Let's take a look at how we can do that:

SELECT
  SUM(unit_price * quantity) AS total_price
FROM orders o
JOIN order_items oi
  ON o.order_id = oi.order_id
WHERE o.order_id = 10250;

We want to find the total price (before discount) for the order with ID of 10250. As you can see, we use SUM(unit_price * 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 order_items 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 total_price_after_discount.

Stuck? Here's a hint!

The new column should use the following formula:

unit_price * quantity * (1 - discount)