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
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.EmployeeID,
  E.FirstName,
  E.LastName,
  COUNT(*) AS OrdersCount
FROM Orders O
JOIN Employees E
  ON O.EmployeeID = E.EmployeeID
GROUP BY E.EmployeeID,
  E.FirstName,
  E.LastName;

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

SELECT
  E.FirstName,
  E.LastName,
  COUNT(*) AS OrdersCount
FROM Orders O
JOIN Employees E
  ON O.EmployeeID = E.EmployeeID
GROUP BY E.EmployeeID,
  E.FirstName,
  E.LastName;

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. CompanyName
  2. TotalPaid, 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 TotalPaid in descending order.

Stuck? Here's a hint!

Use the following formula to find the total amount paid:

SUM(UnitPrice * Quantity * (1 - Discount)) AS TotalPaid

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

To filter dates, use:

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