Home » SQL & PL/SQL » SQL & PL/SQL » query (oracle 9i)
query [message #329629] Thu, 26 June 2008 02:02 Go to next message
msafana
Messages: 31
Registered: July 2007
Member
Hi all,
Please help me in this issue.
I have one table named Transaction with columns Amount and Type

Transaction
Amount Type
1000 Credit
5000 Credit
6000 Credit
8000 Debit
1000 Debit

I want to write the query that give result by adding amount of Credit type and deduct the debit value means we need to sum (1000+5000+6000) and (8000+1000) and the result should be the difference of those two values.

Can u please help me in this query.Thanks to all.




Re: query [message #329634 is a reply to message #329629] Thu, 26 June 2008 02:10 Go to previous messageGo to next message
seyed456
Messages: 220
Registered: May 2008
Location: south india .
Senior Member


hi.
can u post the create and insert statements? ?
Re: query [message #329639 is a reply to message #329629] Thu, 26 June 2008 02:23 Go to previous messageGo to next message
nasir_mughal
Messages: 121
Registered: April 2007
Location: Karachi
Senior Member
try this

select nvl(a.credit,0)-nvl(b.debit,0) diff
from (select sum(amount) credit from transactions where type='Credit') a,
(select sum(amount) debit from transactions where type='Debit') b
Re: query [message #329642 is a reply to message #329629] Thu, 26 June 2008 02:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use sum along with decode on type column.

by the way, "type" is a reserved word, don't use it.

Regards
Michel
Re: query [message #329645 is a reply to message #329639] Thu, 26 June 2008 02:31 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, if you want to do it in a single read of the table (always useful for large tables):
drop table transaction_test;

create table transaction_test (amount number, tran_type  varchar2(10));

insert into transaction_test values (1000, 'Credit');
insert into transaction_test values (5000, 'Credit');
insert into transaction_test values (6000, 'Credit');
insert into transaction_test values (8000, 'Debit');
insert into transaction_test values (1000, 'Debit');

select sum(amount * (case when tran_type = 'Debit' then -1 else 1 end))
from   transaction_Test;
Re: query [message #329651 is a reply to message #329645] Thu, 26 June 2008 02:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64145
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It seems you now make up your mind to post full solution to newbie/student.

Regards
Michel
Re: query [message #329665 is a reply to message #329651] Thu, 26 June 2008 03:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
He'd already been given a full solution. At least this way he gets a better one.
Re: query [message #329681 is a reply to message #329629] Thu, 26 June 2008 04:01 Go to previous messageGo to next message
msafana
Messages: 31
Registered: July 2007
Member

Hi all,
Thanks for your suggestions and for solution.
Re: query [message #329707 is a reply to message #329681] Thu, 26 June 2008 05:20 Go to previous messageGo to next message
micro_oracle
Messages: 41
Registered: December 2007
Location: UAE
Member

hi all

I think this is the better solution :

select sum(decode(tran_type,'Credit',nvl(amount,0),'Debit',-nvl(amount,0))) AS Total
 from transaction_test;


Thanks
Re: query [message #329722 is a reply to message #329629] Thu, 26 June 2008 05:52 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
one more solution

SQL> select * from transaction;

    AMOUT TYPE
--------- ----------
     1000 Credit
     5000 Credit
     6000 Credit
     8000 Debit
     1000 Debit

SQL> ed
Wrote file afiedt.buf

  1  select c.credit,d.debit,c.credit-d.debit as diff from
  2  (select sum(amout) as credit from transaction
  3  where type='Credit'
  4  ) c,
  5  (select sum(amout) as debit from transaction
  6  where type='Debit'
  7* ) d
SQL> /

   CREDIT     DEBIT      DIFF
--------- --------- ---------
    12000      9000      3000



regards,
kaushal
Re: query [message #329727 is a reply to message #329707] Thu, 26 June 2008 06:16 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
micro_oracle wrote on Thu, 26 June 2008 12:20
hi all

I think this is the better solution :

select sum(decode(tran_type,'Credit',nvl(amount,0),'Debit',-nvl(amount,0))) AS Total
 from transaction_test;


Thanks

Better than which?
And why do you think this is better?
Re: query [message #329733 is a reply to message #329722] Thu, 26 June 2008 06:36 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
There's still no need for two copies of the table - you can provide your additional data from a single pass too:
SQL> select sum(case when tran_type = 'Credit' then amount else 0 end) credits
  2        ,sum(case when tran_type = 'Debit'  then amount else 0 end) debits
  3        ,sum(amount * (case when tran_type = 'Debit' then -1 else 1 end)) total
  4  from   transaction_Test;

   CREDITS     DEBITS      TOTAL
---------- ---------- ----------
     12000       9000       3000

Previous Topic: regarding update statement
Next Topic: Problems with special HTML characters and PLSQL packages
Goto Forum:
  


Current Time: Fri Dec 09 11:50:30 CST 2016

Total time taken to generate the page: 0.08627 seconds