Home » SQL & PL/SQL » SQL & PL/SQL » how to sum the amounts
how to sum the amounts [message #689698] Tue, 26 March 2024 06:46 Go to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Hi All,

Could you please help me with the SQL to sum the +ve and -ve amounts of column with SQL query.
for example:
below is the table output with two columns TYPE and AMOUNTS.

TYPE	AMOUNTS	
US_A	37285782	
US_A	55222	
US_A	-468129.01	
US_A	326498.56	
US_A	-63982	
expected output as below:
		
TYPE 	-ve Amount	+ve Amount
US_A	-532111.01	37667502.56

Requesting you please help me.

Thank you.

Regards
Suji
Re: how to sum the amounts [message #689700 is a reply to message #689698] Tue, 26 March 2024 07:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create statements for all objects so that we will be able work to reproduce what you have.

Also always post your Oracle version, with 4 decimals (query v$version), as often solution depends on it.


One way (not tested as I have not your table and data):
select type, sum(least(amounts,0)) "-ve", sum(greatest(amounts,0)) "+ve"
from mytable
group by type
/

[Updated on: Tue, 26 March 2024 13:18]

Report message to a moderator

Re: how to sum the amounts [message #689701 is a reply to message #689698] Tue, 26 March 2024 07:35 Go to previous messageGo to next message
John Watson
Messages: 8931
Registered: January 2010
Location: Global Village
Senior Member
Here's another possible solution,
select type,sum(pos),sum(neg) from
 (select type,amount pos,null neg from t1 where amount > 0
 union all
 select type,null,amount from t1 where amount < 0)
 group by type;
I sometimes think that the set operators are under used by many developers. In this case, if there were an index and histograms on (AMOUNT,TYPE) the compound query might be very efficient.
Re: how to sum the amounts [message #689702 is a reply to message #689701] Tue, 26 March 2024 08:01 Go to previous messageGo to next message
Littlefoot
Messages: 21809
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Conditional aggregation is yet another option:

select type, 
       sum (case when amounts < 0 then amounts end) neg,
       sum (case when amounts > 0 then amounts end) pos
from your_table
group by type
Re: how to sum the amounts [message #689703 is a reply to message #689701] Tue, 26 March 2024 08:02 Go to previous messageGo to next message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Thanks for your response.
Here you go with the table and insert statements for data.

CREATE TABLE GL_LEDGER
 (TYPE VARCHAR(10),
  ACCOUNT VARCHAR(10),
  AMOUNTS1 INT);

INSERT INTO GL_LEDGER VALUES ('US_1', 'A', -100);
INSERT INTO GL_LEDGER VALUES ('US_1', 'B', 100);
INSERT INTO GL_LEDGER VALUES ('US_1', 'C', 200);
INSERT INTO GL_LEDGER VALUES ('US_1', 'D', -200);
INSERT INTO GL_LEDGER VALUES ('US_1', 'E', -300);
INSERT INTO GL_LEDGER VALUES ('US_1', 'F', 300);
INSERT INTO GL_LEDGER VALUES ('US_1', 'G', -100);

INSERT INTO GL_LEDGER VALUES ('US_2', 'A', 100);
INSERT INTO GL_LEDGER VALUES ('US_2', 'B', 100);
INSERT INTO GL_LEDGER VALUES ('US_2', 'C', 200);
INSERT INTO GL_LEDGER VALUES ('US_2', 'D', 200);
INSERT INTO GL_LEDGER VALUES ('US_2', 'E', -300);
INSERT INTO GL_LEDGER VALUES ('US_2', 'F', -300);
INSERT INTO GL_LEDGER VALUES ('US_2', 'G', 100);
output should be as below:

TYPE   +VE-AMOUNT   -VE-AMOUNT
US_1     600         -700
US_2     700         -600

Thank you.

Regards
Suji
Re: how to sum the amounts [message #689704 is a reply to message #689703] Tue, 26 March 2024 08:49 Go to previous messageGo to next message
cookiemonster
Messages: 13923
Registered: September 2008
Location: Rainy Manchester
Senior Member
At this point you've been given 3 ways to do this. They should all work.
Re: how to sum the amounts [message #689705 is a reply to message #689704] Tue, 26 March 2024 09:56 Go to previous message
suji6281
Messages: 134
Registered: September 2014
Senior Member
Yeap, All options are working. Thank you
Previous Topic: Do users ever create tables in these schemas?
Next Topic: Getting same sequence number in select query
Goto Forum:
  


Current Time: Sat May 18 23:24:38 CDT 2024