getting sum from two diffrent tables sql

开发者_如何学编程i have two tables with names stock_in and stock_out with following colums

stock_in (recid,itemid,units)

stock_out (recid,itemid,units)

what i want is to get sum of units from both tables and then calculate remaining units from stock_in to stock_out where i can specify the itemid for any item

Thanks

---

SELECT
  (SELECT SUM(units) FROM stock_in WHERE itemid = 7) -
  (SELECT SUM(units) FROM stock_out WHERE itemid = 7)
  AS difference;

---

select
    stock_in.itemid,
    sum_stock_in.sum as sum_in,
    sum_stock_in.sum as sum_out
from
    stock_in
    left join (
        select itemid, sum(units) as sum from stock_in group by itemid
    ) as sum_stock_in on sum_stock_in.itemid = stock_in.itemid
    left join (
        select itemid, sum(units) as sum from stock_out group by itemid
    ) as sum_stock_out on sum_stock_out.itemid = stock_in.itemid
where
    stock_in.itemid in (1, 2, 3)

-- edit:
group by
    stock_in.itemid

In this query, it is assumed that stock_out is a subset of stock_in, i.e., stock_in contains every possible itemid.

---

Make a stored procedure out of it

SELECT @SUM1=SUM(*)
FROM STOCK_IN
SELECT @SUM2=SUM(*)
FROM STOCK_OUT

SELECT @SUM1-@SUM2

Thats the best solution I can think of, the one mentioned by Dan is not working since using two from, the actual work done is inner join

---

Since this is under PHP:

$queryString = "SELECT
  SUM(stock_in.units) - SUM(stock_out.units) as difference
FROM
  stock_in
INNER JOIN stock_out
ON stock_in.itemid=stock_out.itemid
WHERE stock_in.itemid = ".$value_to_query;
$result = mysql_query($queryString);

Tests:

create database sumgetter;
use sumgetter;
CREATE TABLE stock_in (
         recid INT,
         itemid INT,
         units INT
);
CREATE TABLE stock_out (
         recid INT,
         itemid INT,
         units INT
);
INSERT INTO stock_in VALUES (1, 1, 2);
INSERT INTO stock_out VALUES (1, 1, 3);
INSERT INTO stock_in VALUES (2, 2, 2);
INSERT INTO stock_out VALUES (2, 2, 2);

SELECT
  SUM(stock_in.units) - SUM(stock_out.units) as difference
FROM
  stock_in
INNER JOIN stock_out
ON stock_in.itemid=stock_out.itemid
WHERE stock_in.itemid = 1;

//result

+------------+
| difference |
+------------+
|         -1 |
+------------+
1 row in set (0.00 sec)