Home » SQL & PL/SQL » SQL & PL/SQL » Running Balance (Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production)
Running Balance [message #686428] |
Mon, 12 September 2022 02:55 |
|
shawaj
Messages: 89 Registered: January 2016
|
Member |
|
|
Hello All,
I have a SAL table. I need to write a query that produce running balance for every group(EID) except last month in that group.
Please help me.
Thanks..
CREATE TABLE SAL(
EID NUMBER(3),
ID_MONTH NUMBER(3),
SAL NUMBER(15,2)
);
ALTER TABLE SAL ADD CONSTRAINT PK_ID_M PRIMARY KEY (EID,ID_MONTH);
INSERT INTO SAL VALUES (1,1,500);
INSERT INTO SAL VALUES (2,1,600);
INSERT INTO SAL VALUES (3,1,700);
INSERT INTO SAL VALUES (1,2,800);
INSERT INTO SAL VALUES (2,2,900);
INSERT INTO SAL VALUES (3,2,1000);
INSERT INTO SAL VALUES (1,3,1100);
INSERT INTO SAL VALUES (2,3,1200);
INSERT INTO SAL VALUES (3,3,1300);
INSERT INTO SAL VALUES (1,4,1400);
INSERT INTO SAL VALUES (2,4,1500);
Expected output :
EID ID_MONTH SAL CUMM_SUM
1 1 500 500
1 2 800 1300
1 3 1100 2400
2 1 600 600
2 2 900 1500
2 3 1200 2700
3 1 700 700
3 2 1000 1700
|
|
|
|
|
|
|
Re: Running Balance [message #686434 is a reply to message #686433] |
Mon, 12 September 2022 06:42 |
John Watson
Messages: 8951 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
This is my solution, based on a CTE constructed with a left exclusive outer join to remove the last row from each group:with cte as
(select a.* from sal a left join (select eid,max(id_month) id_month from sal group by eid) b on (a.eid=b.eid and a.id_month=b.id_month)
where b.eid is null and b.id_month is null)
SELECT EID,ID_MONTH,SAL,
SUM(SAL) OVER (PARTITION BY EID ORDER BY SAL) CUMM_SUM
FROM cte
/ I think that the Shawaj-Cadot algorithm is superior: it comes in at a slightly lower cost. However, if you overload your primary key index by including the SAL column, the costs come right down.
|
|
|
Re: Running Balance [message #686435 is a reply to message #686433] |
Mon, 12 September 2022 08:08 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
shawaj wrote on Mon, 12 September 2022 13:26Thanks Michel Cadot for your response.
Its working now ..
select EID,ID_MONTH,SAL,CUMM_SUM
from (
SELECT EID,ID_MONTH,SAL,
SUM(SAL) OVER (PARTITION BY EID ORDER BY SAL) CUMM_SUM,
max(id_month) over (partition by eid) last_month
FROM SAL)
where id_month <> last_month;
Accordingly to your requirement, your ORDER BY clause is not correct:
Quote: produce running balance for every group(EID) except last month in that group
So it appears you have to ORDER BY ID_MONTH not SAL.
The results are the same in your test case because SAL is always increasing with ID_MONTH which may not be always true in real case.
[Updated on: Mon, 12 September 2022 08:31] Report message to a moderator
|
|
|
Re: Running Balance [message #686436 is a reply to message #686435] |
Tue, 13 September 2022 01:31 |
|
shawaj
Messages: 89 Registered: January 2016
|
Member |
|
|
Yes, You are correct and I have modified my query.
SELECT EID,ID_MONTH,SAL, CUMM_SUM FROM (
SELECT EID,ID_MONTH,SAL, SUM(SAL) OVER (PARTITION BY EID ORDER BY ID_MONTH) CUMM_SUM,max(id_month) over (partition by eid ) last_month
FROM SAL)
WHERE ID_MONTH <> last_month;
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:37:06 CDT 2024
|