Chapter 9 Exercise 21

21. Create an OLAP cube to evaluate sales (value and quantity) by model type, state, time, and sales employee.

SELECT Bicycle.SaleState, [LastName] & ", " & [FirstName] AS Salesperson, Format([OrderDate],"yyyy-mm") AS SaleMonth, Bicycle.ModelType, Sum(Bicycle.SalePrice) AS TotalSales, Count(Bicycle.SerialNumber) AS CountSales
FROM Employee INNER JOIN Bicycle ON Employee.EmployeeID = Bicycle.EmployeeID
GROUP BY Bicycle.SaleState, [LastName] & ", " & [FirstName], Format([OrderDate],"yyyy-mm"), Bicycle.ModelType;