# Working with Aggregate Functions - Exercise

Working with Aggregate Functions - Exercise

# Working with Aggregate Functions

Duration: 10 to 20 minutes.

In this exercise, you will practice working with aggregate functions. For all of these questions, it's okay if your result set's rows are in a different order.

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:

```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;