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 Go to next message
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 #686429 is a reply to message #686428] Mon, 12 September 2022 03:14 Go to previous messageGo to next message
John Watson
Messages: 8793
Registered: January 2010
Location: Global Village
Senior Member
Would this be a school homework question?

What SQL have tried so far? Where are you stuck?
Re: Running Balance [message #686430 is a reply to message #686429] Mon, 12 September 2022 04:22 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
Hi John, thanks for your response..
I am not expert of SQL and I tried below query but its not complete ..

SELECT EID,ID_MONTH,SAL,
SUM(SAL) OVER (PARTITION BY EID ORDER BY SAL) CUMM_SUM
FROM SAL;
Re: Running Balance [message #686432 is a reply to message #686430] Mon, 12 September 2022 06:14 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can use MAX function in its analytic form in the same way and exclude the rows where id_month is equal to this max.

Re: Running Balance [message #686433 is a reply to message #686432] Mon, 12 September 2022 06:26 Go to previous messageGo to next message
shawaj
Messages: 89
Registered: January 2016
Member
Thanks 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;
Re: Running Balance [message #686434 is a reply to message #686433] Mon, 12 September 2022 06:42 Go to previous messageGo to next message
John Watson
Messages: 8793
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 Go to previous messageGo to next message
Michel Cadot
Messages: 68341
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
shawaj wrote on Mon, 12 September 2022 13:26
Thanks 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 Go to previous message
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;
Previous Topic: Consuming Rest API from PL/SQL using UTL_HTTP - body not recognized
Next Topic: Combine 2 fields when using Groupby (merged)
Goto Forum:
  


Current Time: Sun Jan 29 03:07:05 CST 2023