Subtracting two values from different table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Subtracting two values from different table

Author

Topic

amzers
Starting Member

1 Post

Posted - 2012-10-19 : 13:44:37

Hello

table1
items stock
banana 40
apple 40
melon 40

table2
items quantity
banana 5
banana 7
apple 3
melon 2
apple 5
__________________
items stock
banana 38
apple 32
melon 48

SELECT table1.items,sum(table1.stock-table2.quantity) from table1,table2 where table1.items=table2.items group by table1.items"
^ i tried this code above but it doesnt seem to work,any help would be needed,a brief explanation...i really needed to learn how to do this

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-10-19 : 14:03:03

Here is the basics. There might be other things you need to do (LEFT OUTER JOIN) depending on your actual data (i.e.: if a Stock item hans't been sold):

DECLARE @table1 TABLE (items varchar(50), stock int)
INSERT @table1 VALUES
('banana', 40),
('apple', 40),
('melon', 40)

DECLARE @table2 TABLE (items varchar(50), quantity int)
INSERT @table2 VALUES
('banana', 5),
('banana', 7),
('apple', 3),
('melon', 2),
('apple', 5)

SELECT 
    a.items, 
    (a.stock - b.quantity) AS stock
FROM
    @table1 AS a
INNER JOIN
    (
        SELECT items, SUM(quantity) AS quantity
        FROM @table2
        GROUP BY items
    ) AS b
    ON a.items = b.items

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-19 : 22:31:11

i would say LEFT JOIN is more logical as there can be items that are not sold yet

in that case make stock expression as

a.stock - COALESCE(b.quantity,0) AS stock

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources