Hi All,
I have data like below.
select 1357 Order_num, 10 Order_amount, 'ABC-123'invoice_num, 1 Order_qty, '123.110.1234' Order_acct from dual
union all
select 1357 Order_num, 20 Order_amount, 'ABC-123'invoice_num, 1 Order_qty, '123.110.1234' Order_acct from dual
union all
select 1357 Order_num, 30 Order_amount, 'ABC-123'invoice_num, 1 Order_qty, '123.110.1234' Order_acct from dual
union all
select 2715 Order_num, 40 Order_amount, 'ABC-123'invoice_num, 2 Order_qty, '123.143.1234' Order_acct from dual
union all
select 2715 Order_num, 50 Order_amount, 'ABC-123'invoice_num, 2 Order_qty, '123.143.1234' Order_acct from dual
union all
select 2715 Order_num, 60 Order_amount, 'ABC-123'invoice_num, 2 Order_qty, '123.143.1234' Order_acct from dual
union all
select 1454 Order_num, 10 Order_amount, 'ABB-123'invoice_num, 1 Order_qty, '124.110.1234' Order_acct from dual
union all
select 1454 Order_num, 20 Order_amount, 'ABB-123'invoice_num, 1 Order_qty, '124.110.1234' Order_acct from dual
union all
select 1454 Order_num, 30 Order_amount, 'ABB-123'invoice_num, 1 Order_qty, '124.110.1234' Order_acct from dual
union all
select 1234 Order_num, 40 Order_amount, 'ABB-123'invoice_num, 2 Order_qty, '143.123.1234' Order_acct from dual
union all
select 1234 Order_num, 50 Order_amount, 'ABB-123'invoice_num, 2 Order_qty, '143.123.1234' Order_acct from dual
union all
select 1234 Order_num, 60 Order_amount, 'ABB-123'invoice_num, 2 Order_qty, '143.123.1234' Order_acct from dual
I want to sum of the Order_amount column values without group by.
i just want to do this in select statement.
I have used SUM(round(((Order_amount * Order_qty)), 2)) OVER(PARTITION BY Order_qty,invoice_num) CURRENT_COST, but it is giving double values.
current output.
| ORDER_NUM | Order_amount | INVOICE_NUM | ORDER_QTY | ORDER_ACCT |
| 1357 | 10 | ABC-123 | 1 | 123.110.1234 |
| 1357 | 20 | ABC-123 | 1 | 123.110.1234 |
| 1357 | 30 | ABC-123 | 1 | 123.110.1234 |
| 2715 | 40 | ABC-123 | 2 | 123.143.1234 |
| 2715 | 50 | ABC-123 | 2 | 123.143.1234 |
| 2715 | 60 | ABC-123 | 2 | 123.143.1234 |
| 1454 | 10 | ABB-123 | 1 | 124.110.1234 |
| 1454 | 20 | ABB-123 | 1 | 124.110.1234 |
| 1454 | 30 | ABB-123 | 1 | 124.110.1234 |
| 1234 | 40 | ABB-123 | 2 | 143.123.1234 |
| 1234 | 50 | ABB-123 | 2 | 143.123.1234 |
| 1234 | 60 | ABB-123 | 2 | 143.123.1234 |
Expected
| ORDER_NUM | Order_amount | INVOICE_NUM | ORDER_QTY | ORDER_ACCT |
| 1357 | 60 | ABC-123 | 1 | 123.110.1234 |
| 2715 | 150 | ABC-123 | 2 | 123.143.1234 |
| 1454 | 60 | ABB-123 | 1 | 124.110.1234 |
| 1234 | 150 | ABB-123 | 2 | 143.123.1234 |
Thank you very much!
Message was edited by: Rajesh123 Corrected Expected output
Message was edited by: Rajesh123 updated with correct expected output
Message was edited by: Rajesh123 Could you please check the Reply#17 has correct and expected output details