I have a table like this

and the output what I want

Where NETT2 is sum of value of TRX_NUMBER, and i only want to use SUM, no matter what i use CASE

Can is possible ?

I tried like this

select segment1 
,jenis_rcv
,gl_date
,trx_number
,nett
,sum(nett) nett2
from SEMUA
group by segment1 
,jenis_rcv
,gl_date
,trx_number
,nett

CodePudding user response：

Use the SUM analytic function with a PARTITION BY but without an ORDER BY clause.

https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf/SUM.html#GUID-5610BE2C-CFE5-446F-A1F7-B924B5663220

sum(nett) over (partition by trx_number) nett2

CodePudding user response：

If you don't need gl_date in your output because you are summing across all dates:

select segment1 
,jenis_rcv
,trx_number
,nett
,sum(nett) nett2
from SEMUA
group by segment1 
,jenis_rcv
,trx_number
,nett

Keeping gl_date makes things a little more complicated, you need to use a PARTITION BY to explain what to sum over:

select segment1 
,jenis_rcv
,gl_date
,trx_number
,SUM(nett)
OVER (PARTITION BY segment1 
,jenis_rcv
,trx_number
ORDER BY gl_date) as nett2
FROM SEMUA