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
10. Choosing information to show
Understanding the difference between various count metrics
Summary

Instruction

Excellent! Let's take a look at the correct query from the previous exercise once again:

SELECT
  e.employee_id,
  e.first_name,
  e.last_name,
  COUNT(*) AS orders_count
FROM orders o
JOIN employees e
  ON o.employee_id = e.employee_id
GROUP BY e.employee_id,
  e.first_name,
  e.last_name;

We're grouping by employee_id, but that doesn't mean we need to include it in the SELECT clause. The following query will work just fine:

SELECT
  e.first_name,
  e.last_name,
  COUNT(*) AS orders_count
FROM orders o
JOIN employees e
  ON o.employee_id = e.employee_id
GROUP BY e.employee_id,
  e.first_name,
  e.last_name;

We've already learned that all columns in the SELECT clause that aren't used in an aggregate function must appear in the GROUP BY clause. The opposite, however, is not true: You don't have to SELECT all columns used in the GROUP BY clause.

Exercise

Which customers paid the most for orders made in June 2016 or July 2016? Show two columns:

  1. company_name
  2. total_paid, calculated as the total price (after discount) paid for all orders made by a given customer in June 2016 or July 2016.

Sort the results by total_paid in descending order.

Stuck? Here's a hint!

Use the following formula to find the total amount paid:

SUM(unit_price * quantity * (1 - discount)) AS total_paid

Remember that you need to group by customer_id, even though the column won't be shown in the report.

To filter dates, use:

WHERE order_date >= '2016-06-01' AND order_date < '2016-08-01'