Working with Aggregate Functions - Exercise

Contact Us or call 1-877-932-8228
Working with Aggregate Functions - Exercise

Working with Aggregate Functions

Duration: 10 to 20 minutes.

In this exercise, you will practice working with aggregate functions.

  1. Create a report that returns the following from the Order_Details table.The report should only return rows for which TotalUnits is less than 200.
  2. Create a report that returns the following from the Products table. The report should only return rows for which the average unit price of a product is greater than 70.
  3. Create a report that returns the following from the Orders table.NumOrders represents the number of orders placed by a certain customer. Only return rows where NumOrders is greater than 15.

Query number 2 above has something strange about it. It is, in fact, a ridiculous query. Why? Try to get the exact same results without using an aggregate function.

Solution:

AdvancedSelects/Solutions/Grouping.sql
SELECT ProductID, SUM(Quantity) AS TotalUnits
FROM Order_Details /* SQL Server users should use "Order Details" */
GROUP BY ProductID
HAVING SUM(Quantity) < 200;

SELECT ProductID, AVG(UnitPrice) AS AveragePrice
FROM Products
GROUP BY ProductID
HAVING AVG(UnitPrice) > 70
ORDER BY AveragePrice;

SELECT CustomerID, COUNT(OrderID) AS NumOrders
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) > 15
ORDER BY NumOrders DESC;

/*CHALLENGE ANSWER:*/
SELECT ProductID, UnitPrice
FROM Products
WHERE UnitPrice > 70
ORDER BY UnitPrice;
Next