0

I have two sql tables, purchase and sent, I want to know the outstanding balance of items at any date. For this I am trying to take sum(puchase.quantity) - sum(sent.quantity) where date<some date. I can have individual queries but I dont know how to combine them.

Here are individual queries:

select item,sum(quantity) from sent where datee <'some date' group by item
select item,sum(quantity) from purchase where datee<'some date' group by item

Please tell me if there is aa better way to get the outstanding balance.

Thanks in advance.

Ali Haider
  • 33
  • 4

2 Answers2

1

I think you want this

Here is working demo SQLFiddle

CREATE TABLE sent
(
item int,
quantity int
);

CREATE TABLE purchase
(
item int,
quantity int
);

insert into sent values(1,4);
insert into sent values(2,7);
insert into sent values(3,9);
insert into sent values(4,5);
insert into sent values(5,9);

insert into purchase values(1,2);
insert into purchase values(2,5);
insert into purchase values(3,3);
insert into purchase values(4,2);
insert into purchase values(5,7);


select sent.item , (sum(IFNULL(sent.quantity,0)) - sum(IFNULL(purchase.quantity,0))) as diff 
FROM sent  , purchase Where sent.item = purchase.item group by sent.item
Dhaval
  • 2,801
  • 20
  • 39
  • I would have written a similar solution. If you will use outer join instead of descartes multiplication, then I will upvote your answer. – Lajos Arpad Sep 14 '13 at 19:20
  • this is not working if we have purchased one item more than once ... e.g., in my case i have purchased one item three times, instead of right answer 18, the query is giving me 54 .. – Ali Haider Sep 14 '13 at 19:32
  • ok, @AliHaider, then please create a query where your summing queries are sub-queries and the resulting relations are named as t1 and t2. Join them using a join condition on item and it should work. – Lajos Arpad Sep 14 '13 at 20:16
  • @LajosArpad, i am really weak at sql, can you please show me the query. It would be of great help. – Ali Haider Sep 14 '13 at 21:25
  • Not really, as I have no time to test it and I will get down-votes if there is a typo. – Lajos Arpad Sep 15 '13 at 00:10
1

Maybe there's a more succint way to get what you want, but the folliwing should give you correct results:

SELECT s.item, 
        s.qty as total_sent, 
        COALESCE(p.qty,0) as total_purchase, 
        s.qty - COALESCE(p.qty,0) as stock
FROM 
(
  SELECT item, sum(quantity) as qty
  FROM sent 
  GROUP BY item
) s
LEFT JOIN 
(
  SELECT item, sum(quantity) as qty
  FROM purchase
  GROUP BY item
) p
ON p.item = s.item
Juan Pablo Califano
  • 12,213
  • 5
  • 29
  • 42