CODEWITHSUNDEEP
sqlpostgresqlgroup-bysumgreatest-n-per-group
TheDataGuy
TheDataGuy

Reputation: 3108

SQL - SUM but filter the 2 max column

I have a table like below.

qid  segment task    before  after   diff
------------------------------------------
1       0    aaa       5     5       0
1       0    aaa       5     5       0
1       1    aaa       15    15      0
1       1    aaa       10    5      10
1       0    aaa       10    5       5
3       0    bbb       10    4       6

For each task I need to sum the before,after and diff values.But the I want to do this operation only on the High qid and segment.

For eg: for task A,

This is row I want to take for task A.

For task B,

The last row is the one I want to calculate.

Expected output:

task    before  after   diff
-----------------------------
aaa       25    20      10
bbb       10    4       6

Upvotes: 0

Views: 96

Answers (2)

zealous
zealous

Reputation: 7503

Try with dense_rank. here is the demo.

with cte as
(
    select
        *,
        dense_rank() over (partition by task order by qid desc, segment desc) as rnk
    from myTable
)

select
    task,
    sum(before) as before,
    sum(after)  as after,
    sum(diff) as  diff
from cte
where rnk = 1
group by
    task

outoput:

| task | before | after | diff |
| ---- | ------ | ----- | ---- |
| aaa  | 25     | 20    | 10   |
| bbb  | 10     | 4     | 6    |

Upvotes: 0

GMB
GMB

Reputation: 222432

You can use aggregation and distinct on:

select distinct on (task) 
    qid, 
    segment, 
    task, 
    sum(before) before,
    sum(after) after,
    sum(diff) diff
from mytable t
group by task, qid, segment
order by task, qid desc, segment desc

Upvotes: 2

Related Questions