How to order the result of ROLLUP by each groups total-CodePudding

So I have the following query that produces the following result:

    actname    | year     | tickets
--------------- ---------- ---------
 Join Division | 2016     |       2
 Join Division | 2018     |       2
 Join Division | 2020     |       3
 Join Division | Total    |       7 <<<
 QLS           | 2018     |       2
 QLS           | 2019     |       1
 QLS           | Total    |       3 <<<
 Scalar Swift  | 2017     |       3
 Scalar Swift  | 2018     |       1
 Scalar Swift  | 2019     |       1
 Scalar Swift  | Total    |       5 <<<
 The Selecter  | 2017     |       4
 The Selecter  | 2018     |       4
 The Selecter  | Total    |       8 <<<
 The Where     | 2016     |       1
 The Where     | 2017     |       3
 The Where     | 2018     |       5
 The Where     | 2020     |       4
 The Where     | Total    |      13 <<<
 ViewBee 40    | 2017     |       3
 ViewBee 40    | 2018     |       1
 ViewBee 40    | Total    |       4 <<<

The problem I have is that I want to re-order the results such that the group with the lowest Total occurs first, such that the results would look like this:

    actname    | year     | tickets
--------------- ---------- ---------
 QLS           | 2018     |       2
 QLS           | 2019     |       1
 QLS           | Total    |       3 <<<
 ViewBee 40    | 2017     |       3
 ViewBee 40    | 2018     |       1
 ViewBee 40    | Total    |       4 <<<
 Scalar Swift  | 2017     |       3
 Scalar Swift  | 2018     |       1
 Scalar Swift  | 2019     |       1
 Scalar Swift  | Total    |       5 <<<
 Join Division | 2016     |       2
 Join Division | 2018     |       2
 Join Division | 2020     |       3
 Join Division | Total    |       7 <<<
 The Selecter  | 2017     |       4
 The Selecter  | 2018     |       4
 The Selecter  | Total    |       8 <<<
 The Where     | 2016     |       1
 The Where     | 2017     |       3
 The Where     | 2018     |       5
 The Where     | 2020     |       4
 The Where     | Total    |      13 <<<

I'm obtaining the results by using the following GROUP:

GROUP BY actname, ROLLUP(year)

Which is combining all the ticket amounts of the same actname and year together.

I can provide the full query if necessary!

Thanks

CodePudding user response：

Using window function (which is sum() in this case) you can set value to groups (groups are partitioned by actname column) , so now every group from actname column, have same value, as its own row where year='Total'.

Then simply sort by that new column, something like this:

with t(actname, year, tickets) as (
VALUES
('Join Division','2016',2),
('Join Division','2018',2),
('Join Division','2020',3),
('Join Division','Total',7),
('QLS','2018',2),
('QLS','2019',1),
('QLS','Total',3 ),
('Scalar Swift','2017',3),
('Scalar Swift','2018',1),
('Scalar Swift','2019',1),
('Scalar Swift','Total',5 ),
('The Selecter','2017',4),
('The Selecter','2018',4),
('The Selecter','Total',8 ),
('The Where','2016',1),
('The Where','2017',3),
('The Where','2018',5),
('The Where','2020',4),
('The Where','Total',13 ),
('ViewBee 40','2017',3),
('ViewBee 40','2018',1),
('ViewBee 40','Total',4 )
)
SELECT * FROM (
    select *,  sum(case when year = 'Total' then tickets end) over(partition by actname) sm from t
) tt
ORDER BY sm, year