How to split a group by query?

AlbinA 0
Viewed 40

I have a table like this:

Id 1 Id 2 Amount
001 AAA 10
001 AAA 10
001 AAA 10
001 AAA 10
001 AAA 10
001 AAA 10
001 AAA 10
001 AAA 10
001 AAA 10
001 AAA 10

If I do a select query like

SELECT id1,id2,sum(amount),count(*) from table group by id1,id2;

I get the answer like;

001 |AAA |100 |10

What I want to do is split this into two aggregates, so that the first 8 rows will have one aggregate sum and the next 2 should have the sum of next two rows [8 is the cut off].

For example, the answer should be like:

001 |AAA |80  |8
001 |AAA |20  |2

Is it possible to achieve this?

As mentioned in comments, the order of 8 rows+2 rows doesn't matter just that the records in both batches be mutually exclusive. Thanks @SadlyFullStack for the answer!

oracle sql
1 Answers

Since order doesn't matter, we can use ceil and row_number with a null order clause in a CTE to flag every 8 rows with a number. Then, we have another query to aggregate those based on the group number we created:

with grouped as
(
    select tbl.*
        , ceil(row_number() over (order by null) / 8) grp_num
    from tbl
)
select id1
    , id2
    , sum(amount)
    , count(*)
from grouped
group by id1, id2, grp_num
Sadly Full Stack0
Related