SQL order groups of data-CodePudding

Here I have a data sample:

Title	Size	Count
First	3	14
First	5	3
Second	2	5
First	2	10
Third	3	10
Second	3	4
Third	2	9
Third	5	11
Second	5	4

Now I want to sort the data with following rules:

Put the records with same title together: First followed by First, Second followed by Second.

Then for each group, order them by size;

For groups, order them in the sum of count of each group, like: sum of First is 14 3 10=27, Second is 5 4 4=13, Third is 10 9 11=30.

The result I want:

Title	Size	Count
Second	2	5
Second	3	4
Second	5	4
First	2	10
First	3	14
First	5	3
Third	2	9
Third	3	10
Third	5	11

CodePudding user response：

untested notepad scribble

SELECT Title, Size, "Count"
FROM
(
   SELECT Title, Size, "Count"
   , SUM("Count") OVER (PARTITION BY Title) AS TotalCount
   FROM yourtable
) q
ORDER BY TotalCount, Title, Size

CodePudding user response：

You can join the results of the group count sums back onto the main table, using the sums in the order by clause:

select t.* from tbl t join 
       (select t1.title, sum(t1.cnt) s from tbl t1 group by t1.title) t2 
on t.title = t2.title order by t2.s, t.title, t.size;