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
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.OrderID,
  C.CompanyName AS CustomerCompanyName, 
  SUM(UnitPrice * Quantity) AS TotalPrice
FROM Orders O
JOIN Customers C
  ON O.CustomerID = C.CustomerID
JOIN OrderItems OI
  ON O.OrderID = OI.OrderID
WHERE O.ShipCountry = N'France'
GROUP BY O.OrderID, C.CompanyName;

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 OrderID, we also added CompanyName 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: EmployeeID, FirstName, LastName, and the number of orders processed as OrdersCount.

Stuck? Here's a hint!

The ID of the employee processing an order is stored in the EmployeeID column of the Orders table. Join the Orders and Employees tables using the two EmployeeID columns. Remember to include three columns in the GROUP BY clause: EmployeeID, FirstName, and LastName.