Database Concepts - Data Warehouse Systems: Design and Implementation - page 37

is applied over each group rather than over each tuple. Finally, the result can
be sorted with the ORDER BY clause, where every attribute in the list can be
ordered either in ascending or descending order by specifying ASC or DESC ,
respectively.
We next present some examples of aggregate SQL queries, more complex
ones will be presented later in the topic. We start with the query “Total
number of orders handled by each employee, in descending order of number
of orders. Only list employees that handled more than 100 orders.”

SELECT EmployeeID, COUNT(*) AS OrdersByEmployee
FROM Orders
GROUP BY EmployeeID
HAVING COUNT(*) > 100
ORDER BY COUNT(*) DESC

The result below shows that employee 4 is the one that handled the
highest number of orders. Basically, to process this query, the SQL engine
sorts the table Orders by EmployeeID (the attribute associated with the
aggregate function) and counts the number of tuples corresponding to the
same employee, by scanning the ordered table. Thus, for example, there are
156 tuples in the table Orders with EmployeeID equal to 4.

EmployeeID OrdersByEmployee
4

156

3 127

1 123

8 104

Consider now the query “For customers from Germany, list the total
quantity of each product ordered. Order the result by customer ID, in
ascending order, and by quantity of product ordered, in descending order.”

SELECT C.CustomerID, D.ProductID, SUM(Quantity) AS TotalQty
FROM Orders O JOIN Customers C ON O.CustomerID = C.CustomerID
JOIN OrderDetails D ON O.OrderID = D.OrderID
WHERE C.Country = ' Germany '
GROUP BY C.CustomerID, D.ProductID
ORDER BY C.CustomerID ASC, SUM(Quantity) DESC

This query starts by joining three tables: Orders , Customers (where we
have the country information), and OrderDetails (where we have the quantity
ordered for each product in each order). Then, the query selects the customers
from Germany. We then group by pairs ( CustomerID, ProductID ), and for
each group, we take the sum in the attribute Quantity .Below,weshowthe
result and how it is built. On the table to the left, we show the result of
the join, ordered by CustomerID and ProductID . This is the state of the table
just before grouping takes place. Each tuple represents the appearance of a