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

Introduction
Providing detailed information and counting objects
Calculating metrics for multiple business objects
7. Order values for multiple orders
Understanding the difference between various count metrics
Summary

Instruction

Good job!

We know how to calculate the total price for a single order. However, real-world reports are typically more complicated. For example, you often need to provide the total values for multiple orders. The query below shows you how to do this:

SELECT
  o.order_id,
  c.company_name AS customer_company_name, 
  SUM(unit_price * quantity) AS total_price
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
JOIN order_items oi
  ON o.order_id = oi.order_id
WHERE o.ship_country = 'France'
GROUP BY o.order_id, c.company_name;

We want to find the total price (before discount) for each order shipped to France. We also want the order ID and the name of the company that placed the order. That's why we used a GROUP BY clause.

Even though it would be enough to group the rows by order_id, we also added company_name in the GROUP BY. This is because every column in the SELECT that isn't used with an aggregate function (e.g., COUNT(), SUM(), etc.) must appear in the GROUP BY clause. Otherwise, the query won't work.

Exercise

We want to know the number of orders processed by each employee. Show the following columns: employee_id, first_name, last_name, and the number of orders processed as orders_count.

Stuck? Here's a hint!

The ID of the employee processing an order is stored in the employee_id column of the orders table. Join the orders and employees tables using the two employee_id columns. Remember to include three columns in the GROUP BY clause: employee_id, first_name, and last_name.