How to order by SUM() DESC when using WITH ROLLUP?

Question

I want to use SELECT WITH ROLLUP in MariaDB to get a summary row in the result set, but also order the remaining rows by SUM() DESC. How to best achieve this?

Using the example from the linked documentation page:

CREATE TABLE booksales ( 
  country VARCHAR(35), genre ENUM('fiction','non-fiction'), year YEAR, sales INT);

INSERT INTO booksales VALUES
  ('Senegal','fiction',2014,12234), ('Senegal','fiction',2015,15647),
  ('Senegal','non-fiction',2014,64980), ('Senegal','non-fiction',2015,78901),
  ('Paraguay','fiction',2014,87970), ('Paraguay','fiction',2015,76940),
  ('Paraguay','non-fiction',2014,8760), ('Paraguay','non-fiction',2015,9030);


SELECT year, SUM(sales) FROM booksales GROUP BY year WITH ROLLUP;
+------+------------+
| year | SUM(sales) |
+------+------------+
| 2014 |     173944 |
| 2015 |     180518 |
| NULL |     354462 |
+------+------------+

How do I order the rows by SUM(sales) DESC while still having the summary row at the end?

Answer 1

There may be simpler ways to achieve this, but given that the effect of a rollup is to add an additional summary row with 'NULL' values for the columns specified in the GROUP BY, if you wrap the query in a CTE or derived table, you can reason over the result (after giving the SUM column a name) and then manually manipulate the order by in fashion:

WITH Cte AS
(
    SELECT 
      year,
      SUM(sales) AS TotSales
    FROM
      booksales
    GROUP BY 
      year ASC WITH ROLLUP
)
SELECT * 
FROM Cte
ORDER BY CASE WHEN year IS NOT NULL THEN TotSales * -1 ELSE 0 END ASC;

(In the above I'm assuming the SUM has to be positive - if not you'll need to find another devious way to ensure the NULL year is placed last)

Sql Fiddle Example