Home » Developer & Programmer » Forms » Having problems with summing values
Having problems with summing values [message #642943] Wed, 23 September 2015 06:15 Go to next message
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.
Re: Having problems with summing values [message #642945 is a reply to message #642943] Wed, 23 September 2015 06:33 Go to previous message
Littlefoot
Messages: 21806
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;
Previous Topic: forms (merged)
Next Topic: POPULATE_GROUP_WITH_QUERY by using procedure
Goto Forum:
  


Current Time: Thu Mar 28 17:47:21 CDT 2024