In Database System Concepts
The pivot clause by itself does not compute the subtotals we saw in the pivot table from Figure 5.17. However, we can first generate the relational representation shown in Figure 5.21, as outlined shortly, and then apply the pivot clause on that representation to get an equivalent result. In this case, the value
allmust also be listed in the for clause, and the order by clause needs to be modified to orderallat the end.
My attempt is but I am not sure how to order all at the end:
SELECT *
FROM (SELECT item name, color, sum(quantity)
FROM sales
GROUP BY CUBE(item name, color) ) as source_table
PIVOT SUM(quantity)
FOR color in ('dark', 'pastel', 'white', 'all'))
ORDER BY item_name -- TODO: how to oder all at the end?
Thanks.


