How to order id's using subtotal from another column in PostgreSQL-CodePudding

I have a table returned by a select query. Example :

id |  day   | count | 
-- | ------ | ----- | 
1  |  71    |   3   | 
1  |  70    |   2   |
1  |Subtotal|   5   |
2  |  70    |   5   |
2  |  71    |   2   |
2  |  69    |   2   |
2  |Subtotal|   9   |
3  |  69    |   1   |
3  |  70    |   1   |
3  |Subtotal|   2   |

the day column contains text values (so varchar)

subtotal is the sum of the counts for an id (e.g. id 2 has subtotal of 5 2 2 = 9)

I now want to order this table so the id’s with the lowest subtotal count come first, and then ordered by day with subtotal at the end (like before)

Expected output:

id |  day   | count | 
-- | ------ | ----- | 
3  |  69    |   1   | 
3  |  70    |   1   |
3  |Subtotal|   2   |
1  |  70    |   2   |
1  |  71    |   3   |
1  |Subtotal|   5   |
2  |  69    |   2   |
2  |  70    |   5   |
2  |  71    |   2   |
2  |Subtotal|   9   |

I can't figure out how to order based on subtotal only ?

i've tried multiple order by (eg: ORDER BY day = 'Subtotal' & a mix of others) and using window functions but none are helping. Cheers !

CodePudding user response：

Not sure if it's directly applicable to your source query (since you haven't included it) however the ordering you require on the sample data can be done with:

order by Max(count) over(partition by id), day

Note - ordering by day works with your sample data but as it's a string it will not honour numeric ordering, this should really be ordered by the source of the numerical value - again since we don't have your actual query I can't suggest anything more applicable but I'm sure you can substitute the correct column/expression.

CodePudding user response：

I just crated table with 3 columns and tried to reproduce your expected result. I assume that there might be a problem ordering by day, subtotal would be always on top, but it seems as working solution.

create table test
(
    id int,
    day varchar(15),
    count int
)

insert into test
values 
(1,'71',3),
(1,'70',2),
(2,'70',5),
(2,'71',2),
(2,'69',2),
(3,'69',1),
(3,'70',1)

select id, day, count
from
(
    select id, day, sum(count) as count
    from test
    group by id, rollup(day)
) as t
order by Max(count) over(partition by id), day