Group rows 1-50, 51-75, 76-100

[Akumadevil]

Hi,

I have a query that returns 100 rows. Each row has an amount and a name.

What I would like to do is group the 100 rows as follows:

rows 1-50 - sum(amount)
rows 51-75 - sum(amount)
rows 76-100 - sum(amount)

so the end output might be:

SUM(AMOUNT)
10000
5000
5000

Any help would be greatly appreciated.

 

[r937]

cannot be done unless you provide a column that can be sorted on, in order to give relative row numbers

can we see the query?

r937.com | rudy.ca

 

[PruSQLer]

I managed to do it with the following code:


with Order_rows(org_id, row_num)
as (
select accum_amt, row_number() over(order by org_id)
from prst.nvt_org
order by org_id)
,
trim
As (
Select org_id, row_num
from Order_rows
where row_num < 101 )

Select sum(case when row_num < 51 then org_id else 0 end)
, sum (case when row_num between 51 and 75 then org_id else 0 end)
, sum (case when row_num between 76 and 100 then org_id else 0 end)
From trim


This is DB2 which allows you to do temp tables. If your platform offers that, it's doable.

2nd step is only necessary if you want to limit the number of rows.