How can I order ALL at the end when generating a cross tab?

Viewed 43

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 all must also be listed in the for clause, and the order by clause needs to be modified to order all at the end.

enter image description here enter image description here enter image description here

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.

0 Answers
Related