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

Question

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;

What error you are getting? – Ankit Bajpai Feb 09 '23 at 21:28 — Ankit Bajpai, Feb 09 '23 at 21:28

Thorsten Kettner · Answer 1 · 2023-02-09T21:35:11.423

0

Your error is this:

order by transaction_date desc

You aggregate your rows such as to get one result row per column1. But for a column1 there can be many different transaction_date, so which one do you want to sort by? You can use by the column1's minimum or maximum transaction_date for instance. E.g.:

order by max(transaction_date) desc

And as there can be ties (multiple column1 with the same maximum transaction_date), you should get your ORDER BY clause deterministic by adding the column1:

order by max(transaction_date) desc, column1

Now that you have the syntax error resolved and a semantic problem, too, there remains another issue: You select only column1 = '000000000'. Then you group by column1. This gives you one result row. Of these one row(s), you skip twelve :-) You'll get no result row with this query.

edited Feb 09 '23 at 21:35

answered Feb 09 '23 at 21:29

Thorsten Kettner

89,309
7
49
73

I want to order by transaction_date desc because I want to sum the amount in the last 12 months, there are many transaction_date, also when I added max , no rows returned – MuhammedF Feb 09 '23 at 21:40
Yes, as mentioned, if you aggregate your data to one row only, then sorting this one row is already nonsensical, and if you skip that row to get rows 13 to 24, you don't get any row. Look at your query. Read my explanation. Understand what I am saying. – Thorsten Kettner Feb 09 '23 at 21:44

score 0 · Accepted Answer · answered Feb 09 '23 at 21:32

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;

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

2 Answers2