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
Multiple metrics for a single object
3. Multiple metrics for a single object – exercise
Metrics for two groups
Ratios and percentages
Global vs. specific metrics
Summary

Instruction

Good job! Let's do one more exercise of this kind.

Exercise

For each employee, determine their performance for 2016: Compute the total number and the total revenue for orders processed by each employee. Show the employee's first name (FirstName), last name (LastName), and the two metrics in columns named OrderCount (total number of orders processed by the employee) and OrderRevenue (total revenue for orders processed by the employee).

Stuck? Here's a hint!

You need to join three tables. Remember to use DISTINCT when counting the number of orders. Group by not only FirstName and LastName but also by EmployeeID – only the ID is unique (it may happen that two different employees have the same first and last name).

OrderRevenue should be counted as SUM(OI.UnitPrice * OI.Quantity * (1 - OI.Discount)).