Order table by the total count but do not lose the order by names

I have a table, consisting of 3 columns (Person, Year and Count), so for each person, there are several rows with different years and counts and the final row with total count. I want to keep the table ordered by Name, but also order it by the total count.

So the rows should be ordered by sum, but also grouped by the Person and ordered by year. When I am trying to order by sum, of course, both person and years are messed up. Is there a way to sort like this?

Answer

You’ve stored those “total” rows as well? Gosh! Why did you do that?

---

Anyway: if you

• compute rank for rows whose year column is equal to 'total' and
• add case expression into the order by clause,

you might get what you want:

SQL> with sorter as
  2    (select name, cnt,
  3       rank() over (order by cnt) rnk
  4     from test
  5     where year = 'total'
  6    )
  7  select t.*
  8  from test t join sorter s on s.name = t.name
  9  order by s.rnk, case when year = 'total' then '9'
 10                       else year
 11                  end;

NAME YEAR         CNT
---- ----- ----------
John 2018           3
John 2019           2
John total          5
Bob  2017           2
Bob  2019           4
Bob  total          6

6 rows selected.

SQL>