英文:
how to use group by with offset and fetch rows only on oracle
问题
使用offset子句和fetch next子句来对特定行的金额总和进行计算，并使用group by，但出现错误。我使用以下查询：
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;
英文:
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;
答案1
得分: 0
你的错误在于这里：
order by transaction_date desc
你对行进行聚合，以便每个 column1 得到一个结果行。但对于一个 column1，可能有多个不同的 transaction_date，那么你想按哪个进行排序呢？你可以使用 column1 的最小或最大 transaction_date，例如：
order by max(transaction_date) desc
由于可能存在并列的情况（具有相同的最大 transaction_date 的多个 column1），为了使你的 ORDER BY 子句确定性，你应该添加 column1：
order by max(transaction_date) desc, column1
现在，你已经解决了语法错误和语义问题，还剩下另一个问题：你只选择了 column1 = '000000000'。然后你按 column1 进行分组。这将给你一个结果行。在这些结果行中，你跳过了十二个 
使用这个查询将不会得到任何结果行。
英文:
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.
答案2
得分: 0
你的查询失败，因为你试图按 transaction_date 排序，但它既不在 GROUP BY 子句中，也不是 SELECT 子句中的列别名。
你可以通过先在子查询中获取行，然后进行聚合来修复它：
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;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论