Querying Data Warehouses - Data Warehouse Systems: Design and Implementation - page 219

UNION ALL
SELECT S.SupervisorKey, E.EmployeeKey

FROM Supervision S, Employee E

WHERE S.SubordinateKey = E.SupervisorKey )

SELECT T2.EmployeeName,

FORMAT(T2.SalesAmount, ' $###,##0.00 ' ) AS PersonalSales,
FORMAT(T1.TotalSubSales + T2.SalesAmount, ' $###,##0.00 ' )
AS SalesAmount

FROM ( SELECT SupervisorKey, SUM(S.SalesAmount) AS TotalSubSales

FROM Supervision U, SalesByEmp1997 S
WHERE S.EmployeeKey = U.SubordinateKey
GROUP BY SupervisorKey
) T1 JOIN SalesByEmp1997 T2 ON
T1.SupervisorKey = T2.EmployeeKey

UNION
SELECT EmployeeName,

FORMAT(SalesAmount, ' $###,##0.00 ' ) AS PersonalSales,
FORMAT(SalesAmount, ' $###,##0.00 ' )ASSalesAmount

FROM SalesByEmp1997 S

WHERE NOT EXISTS (

SELECT *

FROM Supervision U

WHERE S.EmployeeKey = U.SupervisorKey )

The first temporary table SalesByEmp1997 defined in the WITH clause
computes the total sales by employee. The temporary table Supervision
computes with a recursive query the transitive closure of the supervision
relationship. The main query is composed of the union of two queries.
The first one computes the personal sales and the total sales amount for
supervisors. For this, the inner query in the FROM clause computes the total
amount made by the subordinates of an employee, and the additional join
with the view SalesByEmp1997 is used to obtain the total sales of a supervisor
in order to add the two amounts. Finally, the second query in the union takes
from the SalesByEmp1997 the data from employees who are not supervisors.

Query 6.13. Total sales amount, number of products, and sum of the
quantities sold for each order.
SELECT OrderNo,
FORMAT(SUM(SalesAmount), ' $###,##0.00 ' ) AS SalesAmount,
MAX(OrderLineNo) AS NbProducts, SUM(Quantity) AS Quantity
FROM Sales
GROUP BY OrderNo
ORDER BY OrderNo

Recall that the sales fact table contains both the order number and the order
line number, which constitute a fact dimension. In the query, we group the
sales by order number, and then we apply the SUM and MAX aggregation
functions for obtaining the requested values.

Query 6.14. For each month, total number of orders, total sales amount,
and average sales amount by order.