| how to sum the amounts [message #689698] |
Tue, 26 March 2024 06:46  |
 |
suji6281
Messages: 151
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
TYPE -ve Amount +ve Amount
US_A -532111.01 37667502.56
Thank you.
Regards
Suji
|
|
|
|
| Re: how to sum the amounts [message #689700 is a reply to message #689698] |
Tue, 26 March 2024 07:00  |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 |
John Watson
Messages: 8960
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; |
|
|
|
|
|
| Re: how to sum the amounts [message #689703 is a reply to message #689701] |
Tue, 26 March 2024 08:02 |
|
suji6281
Messages: 151
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);
TYPE +VE-AMOUNT -VE-AMOUNT
US_1 600 -700
US_2 700 -600
Regards
Suji
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
