29. How has the percent share of sales value for Race bikes (to total) changed over time (by year)?
This query might be easier using PivotTable or OLAP queries available
in Chapter 9. Or perhaps in a spreadsheet. Because of the year,
it will be easiest to do this with two queries:
Q1: compute sales of Race bikes by year
Q2: compute sales (value) of all bikes by year
Q1:
SELECT Year([OrderDate]) AS [Year], Sum(Bicycle.SalePrice) AS SumOfSalePrice
FROM Bicycle
WHERE (((Bicycle.ModelType)="Race"))
GROUP BY Year([OrderDate]);
Q2:
SELECT Year([OrderDate]) AS [Year], Sum(Bicycle.SalePrice) AS SumOfSalePrice
FROM Bicycle
GROUP BY Year([OrderDate]);
Then JOIN on year and divide by the total
Q3:
SELECT Q1.Year, [Q1].[SumOfSalePrice]/[Q2].[SumOfSalePrice] AS RacePct
FROM Q2 INNER JOIN Q1 ON Q2.Year = Q1.Year;