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;
WHERE itemid = # clauses to the subqueries
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)
sqlon its own is still a valid tag if (1) you want vendor-agnostic solutions or (2) you want to know about "standard" SQL.