- adapted from [DBCB] Example 20.15, pp. 1076-1078
- Orwell is not selling well.
Let's try to see which books are not doing well:
SELECT title, SUM(price)
FROM Sales NATURAL JOIN Books
WHERE author = 'Orwell'
GROUP BY title;
- Hmmm. All titles by Orwell are doing about the same.
Is this just a recent trend? Let's drill-down:
SELECT title, month, SUM(price)
FROM (Sales NATURAL JOIN Books) JOIN Days ON date = day
WHERE author = 'Orwell'
GROUP BY title, month;
- Wow. Animal Farm has not sold well recently. Are any
particular dealers not pulling their weight? Let's drill-down further:
SELECT dealer, month, SUM(price)
FROM (Sales NATURAL JOIN Books) JOIN Days ON date = day
WHERE author = 'Orwell' AND title = 'Animal Farm'
GROUP BY month, dealer;
- Sales of Animal Farm by month are so small that no
real trends are observable.
Let's partition only by years (roll-up)
and look at only the previous two:
SELECT dealer, year, SUM(price)
FROM (Sales NATURAL JOIN Books) JOIN Days ON date = day
WHERE author = 'Orwell' AND title = 'Animal Farm' AND
(year = 2004 OR year = 2005)
GROUP BY year, dealer;