In ORACLE SQL is it possible to get sum of all different category values in a end row where that individual category ends?

Question

I have a table in which there are different categories. I want to display sum of Total amount for every category values in a row where that individual category ends.IS it possible write a SQL select query for this.

OUTPUT:

category	Amount	Totalamount
A	20
A	10
A	5	35
B	15
B	5
B	14	34
C	25
C	5	30

Answer 1

You need something such as date or timestamp to differentiate one row from the next in case you have a Category with more than one row of the same amount. Here's a solution that uses a CTE to help determine which row should get the total.

create table table1 (
  category varchar(5), 
  amount integer, 
  order_date date
  );

insert all
 into table1 (category, amount, order_date)  values ('A', 20, DATE '2022-01-01')
 into table1 (category, amount, order_date)  values ('A', 10, DATE '2022-01-02')
 into table1 (category, amount, order_date)  values ('A', 5, DATE '2022-01-03')
 into table1 (category, amount, order_date)  values ('B', 15, DATE '2022-02-01')
 into table1 (category, amount, order_date)  values ('B', 5, DATE '2022-02-02')
 into table1 (category, amount, order_date)  values ('B', 14, DATE '2022-02-03')
 into table1 (category, amount, order_date)  values ('C', 25, DATE '2022-03-01')
 into table1 (category, amount, order_date)  values ('C', 5, DATE '2022-03-02')
select 1 from dual;

with cte1 as (select category, max(order_date) as max_date, sum(amount) as total_amount
  from table1
  group by category
  )
select t.category, t.amount,
  case when t.order_date = c.max_date then c.total_amount end total_amount
from table1 t
join cte1 c
  on t.category = c.category
order by 1

CATEGORY	AMOUNT	TOTAL_AMOUNT
A	20	null
A	10	null
A	5	35
B	15	null
B	5	null
B	14	34
C	25	null
C	5	30

fiddle

If you don't want to use date, then randomly sort using something like row_number() over (partition by category order by dbms_random.random) to get a row number and then add the total to the row with the max rn per category. However, that seems like a mess considering you have a date to work with. Or just use a spreadsheet since the output is not typical.