SQL order groups of data

user_8883824 0
Viewed 58

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
postgresql sql
3 Answers

It's an easy sort once you get the total "Count" per Title.
A SUM OVER can be used for that.

SELECT
  q.title AS "Title"
, q.size AS "Size"
, q.count AS "Count"
FROM
(
   SELECT t.title, t.size, t.count
   , SUM(t.count) OVER (PARTITION BY t.title) AS TotalCount
   FROM yourtable t
) q
ORDER BY q.TotalCount, q.title, q.size
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

Demo on db<>fiddle here

user_40034190

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;
user_73267380

Another approach is by using WITH Queries (Common Table Expressions)

with ct
as (
    select title
        ,size
        ,count
        ,sum(count) over (partition by title) sm
    from tbl
    )
select title
      ,size
      ,count
from ct
order by ct.sm,2;
user_36825990
Related