how to use group by with offset and fetch rows only on oracle

February 9, 2023

I want to to make sum of amount of certain rows using offset clause and fetch next rows only and using group by but it gives error, i used the below query

select sum(amount), column1 from table1 where column1 = '000000000' and column2 =0
group by column1 order by transaction_date desc
offset 12 rows
fetch next 12 rows only;

>Solution :

Your query fails as transaction_date, which you are trying to ORDER BY, is not either in the GROUP BY clause or a column alias in the SELECT clause.

You can fix it by fetching the rows first in a sub-query and then aggregating:

SELECT SUM(amount),
       column1
FROM   (
  SELECT amount,
         column1
  FROM   table1
  WHERE  column1 = '000000000'
  AND    column2 =0
  ORDER BY transaction_date DESC
  OFFSET 12 ROWS
  FETCH NEXT 12 ROWS ONLY
)
GROUP BY column1;