OraFAQ Forum: Forms » Having problems with summing values

Home » Developer & Programmer » Forms » Having problems with summing values

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Having problems with summing values [message #642943]

Wed, 23 September 2015 06:15

benphilsyahoocom
Messages: 13
Registered: May 2011
Location: NIGERIA

Junior Member

Dear All,
Please i am having problem in resolving this summation. I have two tables A and B, the first table contains a column named qtyaccepted and on this column i want to sum all the values to get the total value for qtyaccepted; and on table B i have a column named qtyissued in which i want to sum all the values on this column to give me a total sum for the qtyissued column. I want to subtract the sum total(qtyissued) from the sum total(qtyaccepted) to give me a value. Then i wrote a query to which go thus:
--
SELECT sum(a.qtyaccepted - b.qtyissued)
FROM warehouse_receipt a, warehouse_issue b
WHERE a.warehousecode = b.warehousecode
AND b.warehousecode = :warehouse_issue.warehousecode;
---
The query above runs fine but it gives me an incorrect value i.e the total sum(qtyaccepted) = 100 while the Total sum(qtyissued) = 50. The expectant result is 50... But i get a different value i.e -90. Please can anyone help me on this .Thanks I'll await your reply.

Report message to a moderator

Re: Having problems with summing values [message #642945 is a reply to message #642943]

Wed, 23 September 2015 06:33

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

select 
  (select sum(a.qtyaccpted) sum_accepted
   from warehouse_receipt a
   where a.warehousecode = :warehouse_issue.warehousecode
  ) -
  (select sum(b.qtyissued) sum_issued
   from warehouse_issue b
   where b.warehousecode = :warehouse_issue.warehousecode
  )
into ...
from dual;

Or, as it is Forms (which uses PL/SQL), you could split it into two selects:

declare
  l_sum_accepted number;
  l_sum_issued   number;
  l_result       number;
begin
  select sum(a.qtyaccpted) 
    into l_sum_accepted
    from warehouse_receipt a
    where a.warehousecode = :warehouse_issue.warehousecode;

  select sum(b.qtyissued)
    into l_sum_issued
    from warehouse_issue b
    where b.warehousecode = :warehouse_issue.warehousecode;

  l_result := l_sum_accepted - l_sum _issued;
end;

Report message to a moderator

Previous Topic:	forms (merged)
Next Topic:	POPULATE_GROUP_WITH_QUERY by using procedure

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 02 08:00:45 CST 2025