How to subtract two colums from different tables

Question

0.00/5 (No votes)

See more:

Hey guys. How do I subtract the two columns from different tables base on their product codes?

These are my tables:

CREATE TABLE tblproducts (
	product_barcode varchar(50) PRIMARY KEY NOT NULL,
	product_name varchar(50) NOT NULL,
	product_sprice int NOT NULL,
	product_type varchar(50) NOT NULL,
	product_supplier varchar(50) NOT NULL,
	product_unit varchar (50) NOT NULL
);

CREATE TABLE tblstocks (
	stocks_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	stocks_qty int NOT NULL,
	stocks_status varchar(50) NOT NULL,
	stocks_date varchar(100) NOT NULL,
	stocks_month varchar(100) NOT NULL,
	stocks_year varchar(100) NOT NULL,
	stocks_expiration varchar(100) NOT NULL,
	product_barcode varchar(50) NOT NULL FOREIGN KEY REFERENCES tblproducts(product_barcode)
);

CREATE TABLE tblorder(
	order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	order_qty int NOT NULL,
	order_date varchar(100) NOT NULL,
	product_code varchar(50)
);

I want to subtract the stocks.qty on order_qty. How will I do that? Thanks guys.

What I have tried:

SELECT SUM(ts.stocks_qty) - (SELECT SUM(tbo.order_qty) FROM tblorder AS tbo INNER JOIN tblstocks AS ts ON ts.product_barcode = tbo.product_code) AS Stocks FROM tblstocks AS ts INNER JOIN tblorder AS tbo ON tbo.product_code = ts.product_barcode WHERE ts.product_barcode = tbo.product_code AND ts.stocks_status = 'STOCK IN';

Posted 11-Mar-18 17:46pm

John Th

Updated 11-Mar-18 18:57pm

Add a Solution

2 solutions

Solution 1

SELECT (SUM(ts.stocks_qty)-SUM(tbo.order_qty)) AS Stocks
   FROM tblstocks AS ts INNER JOIN tblorder AS tbo
      ON (tbo.product_code = ts.product_barcode)  WHERE ts.stocks_status = 'STOCK IN'
           GROUP BY tbo.product_code ;

Posted 11-Mar-18 18:53pm

Santosh kumar Pithani

Updated 11-Mar-18 19:07pm

v2

Comments

John Th 12-Mar-18 1:01am

Hi Santosh. Assume that order_qty has a value 2 for P-0001(product_code) and stocks_qty has a value 35 for P-0001(product_code), so the stocks will be 33. How will I do that? I've tried your query but I got 74 stocks. Btw thank you for respond.

Santosh kumar Pithani 12-Mar-18 1:05am

You have to do group by ..wait i will improve solution for you.

John Th 12-Mar-18 1:09am

Alright. I'll wait for it.

John Th 12-Mar-18 1:20am

Thanks Santosh. Really appreciate your solution.

Santosh kumar Pithani 12-Mar-18 1:22am

Welcome John:)

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Bryian Tan · Accepted Answer · 2018-03-11T18:58:00

Solution 2

Here is an example, hopefully it will fired you up! Assuming the order date is unique.

SQL

DECLARE @tblproducts TABLE  (
	product_barcode varchar(50) PRIMARY KEY NOT NULL,
	product_name varchar(50) NOT NULL,
	product_sprice int NOT NULL,
	product_type varchar(50) NOT NULL,
	product_supplier varchar(50) NOT NULL,
	product_unit varchar (50) NOT NULL
)

INSERT INTO @tblproducts
	SELECT 'ninja1', 'My Ninja product 1', '123', 'abc','GoGO', 100 UNION
	SELECT 'ninja2', 'My Ninja product 2', '123', 'yyy','JO JO', 200 UNION
	SELECT 'ninja3', 'My Ninja product 3', '222', 'xxx','XX JO', 111 

DECLARE @tblorder TABLE (
	order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	order_qty int NOT NULL,
	order_date varchar(100) NOT NULL,
	product_code varchar(50)
)

DECLARE @tblstocks TABLE (
	stocks_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	stocks_qty int NOT NULL,
	stocks_status varchar(50) NOT NULL,
	stocks_date varchar(100) NOT NULL,
	stocks_month varchar(100) NOT NULL,
	stocks_year varchar(100) NOT NULL,
	stocks_expiration varchar(100) NOT NULL,
	product_barcode varchar(50) NOT NULL 
)

INSERT INTO @tblstocks
	SELECT 100,'STOCK IN', '1/1/2018','Jan',2018,'1/1/2099','ninja1' UNION
	SELECT 40,'STOCK IN', '2/1/2018','Jan',2018,'1/1/2099','ninja2' UNION
	SELECT 30,'STOCK IN', '3/1/2018','Feb',2018,'1/1/2099','ninja1' UNION
	SELECT 50,'STOCK IN', '3/1/2018','Feb',2018,'1/1/2099','ninja3' UNION
	SELECT 50,'STOCK OUT', '3/1/2018','Feb',2018,'1/1/2099','ninja1' 

INSERT INTO @tblorder
	SELECT 5,'1/3/2018','ninja1' UNION
	SELECT 35,'2/3/2018','ninja1' UNION
	SELECT 15,'2/3/2018','ninja2' UNION
	SELECT 15,'3/3/2018','ninja2' 

;WITH cteSumStocks AS (
	SELECT product_barcode, SUM(stocks_qty) 'TotalStock' FROM @tblstocks WHERE stocks_status='STOCK IN'
		GROUP BY product_barcode
) 
, cteSumOrders AS (
	SELECT product_code, SUM(order_qty) 'TotalOrder' FROM @tblorder
		GROUP BY product_code
) SELECT s.product_barcode, p.product_name, s.TotalStock, o.TotalOrder, s.TotalStock - o.TotalOrder 'AvailableStocks' 
	FROM cteSumStocks s
	JOIN cteSumOrders o
	ON s.product_barcode = o.product_code
	JOIN @tblproducts p
	ON s.product_barcode = p.product_barcode

Output:

barcode	name	                TotalStock	TotalOrder	AvailableStocks
ninja1	My Ninja product 1         130	            40	           90
ninja2	My Ninja product 2	    40	            30	           10

Posted 11-Mar-18 18:58pm

Bryian Tan

Updated 12-Mar-18 18:26pm

v2

Comments

John Th 12-Mar-18 1:20am

Thank you Bryian Tan! It works. Godbless you.

John Th 12-Mar-18 23:59pm

How about if I want to join the tblproducts to get the product name? What will be the query? I have tried a query below but it doesn't work.

;WITH sumStocks AS (
SELECT ts.product_barcode, tp.product_name, SUM(stocks_qty) 'TotalStock' FROM tblstocks AS ts INNER JOIN tblproducts AS tp ON tp.product_barcode = ts.product_barcode WHERE ts.stocks_status='STOCK IN'
GROUP BY ts.product_barcode, tp.product_name
)
, sumOrders AS (
SELECT product_code, SUM(order_qty) 'TotalOrder' FROM tblorder
GROUP BY product_code
) SELECT s.product_barcode, s.TotalStock, o.TotalOrder, s.TotalStock - o.TotalOrder 'AvailableStocks' FROM sumStocks s
JOIN sumOrders o
ON s.product_barcode = o.product_code;

Bryian Tan 13-Mar-18 0:25am

see the updated solution.

John Th 13-Mar-18 2:08am

Thanks Bryian.

Bryian Tan 13-Mar-18 2:12am

You're welcome. Don't forget to mark this as a solution if it help so we can close this topic.

John Th 16-Mar-18 11:53am

Is it possible to retrieve if the TotalOrder is 0?

John Th 16-Mar-18 11:55am

What I mean is;

TotalStocks - 130
TotalOrder - 0
AvailableStocks- 130

Is it possible to retrieve it? Even the TotalOrder is 0?? If yes, what will be the query? Thanks!

Bryian Tan 16-Mar-18 12:13pm

You can use a left join. see below example.

DECLARE @tblproducts TABLE  (
	product_barcode varchar(50) PRIMARY KEY NOT NULL,
	product_name varchar(50) NOT NULL,
	product_sprice int NOT NULL,
	product_type varchar(50) NOT NULL,
	product_supplier varchar(50) NOT NULL,
	product_unit varchar (50) NOT NULL
)

INSERT INTO @tblproducts
	SELECT 'ninja1', 'My Ninja product 1', '123', 'abc','GoGO', 100 UNION
	SELECT 'ninja2', 'My Ninja product 2', '123', 'yyy','JO JO', 200 UNION
	SELECT 'ninja3', 'My Ninja product 3', '222', 'xxx','XX JO', 111 

DECLARE @tblorder TABLE (
	order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	order_qty int NOT NULL,
	order_date varchar(100) NOT NULL,
	product_code varchar(50)
)

DECLARE @tblstocks TABLE (
	stocks_id int IDENTITY(1,1) PRIMARY KEY NOT NULL,
	stocks_qty int NOT NULL,
	stocks_status varchar(50) NOT NULL,
	stocks_date varchar(100) NOT NULL,
	stocks_month varchar(100) NOT NULL,
	stocks_year varchar(100) NOT NULL,
	stocks_expiration varchar(100) NOT NULL,
	product_barcode varchar(50) NOT NULL 
)

INSERT INTO @tblstocks
	SELECT 100,'STOCK IN', '1/1/2018','Jan',2018,'1/1/2099','ninja1' UNION
	SELECT 40,'STOCK IN', '2/1/2018','Jan',2018,'1/1/2099','ninja2' UNION
	SELECT 30,'STOCK IN', '3/1/2018','Feb',2018,'1/1/2099','ninja1' UNION
	SELECT 50,'STOCK IN', '3/1/2018','Feb',2018,'1/1/2099','ninja3' UNION
	SELECT 50,'STOCK OUT', '3/1/2018','Feb',2018,'1/1/2099','ninja1' 

INSERT INTO @tblorder
--	SELECT 5,'1/3/2018','ninja1' UNION
	--SELECT 35,'2/3/2018','ninja1' UNION
	--SELECT 15,'2/3/2018','ninja2' UNION
	SELECT 15,'3/3/2018','ninja2' 

;WITH cteSumStocks AS (
	SELECT product_barcode, SUM(stocks_qty) 'TotalStock' FROM @tblstocks WHERE stocks_status='STOCK IN'
		GROUP BY product_barcode
) 
, cteSumOrders AS (
	SELECT product_code, SUM(order_qty) 'TotalOrder' FROM @tblorder
		GROUP BY product_code
) SELECT s.product_barcode, p.product_name, s.TotalStock, ISNULL(o.TotalOrder, 0) TotalOrder, 
		s.TotalStock - ISNULL(o.TotalOrder, 0) 'AvailableStocks' 
	FROM cteSumStocks s
	LEFT JOIN cteSumOrders o
	ON s.product_barcode = o.product_code
	JOIN @tblproducts p
	ON s.product_barcode = p.product_barcode

John Th 16-Mar-18 12:34pm

Thank you Bryian!

John Th 16-Mar-18 15:02pm

How about this one Bryian? How do I retrieve if the size is Null and the stocks is 0? I want to retrieve it, even if there's no size and stocks.

I've tried this code below but it only retrieve the products have size.

Tables:

CREATE TABLE tblproducts (
	product_code varchar(50) NOT NULL PRIMARY KEY,
	product_name varchar(100) NOT NULL,
	product_supplier varchar(50) NOT NULL,
	product_price int NOT NULL
);<pre>

--Table Structure for Products Stock In
<pre>CREATE TABLE tblproducts_stocksin (
	stocks_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	stocksin_size varchar(30) NOT NULL,
	stocksin_stocks int NOT NULL,
	stocksin_status varchar(50) NOT NULL,
	stocksin_Date varchar(100) NOT NULL,
	stocksin_Month varchar(100) NOT NULL,
	stocksin_Year varchar(100) NOT NULL,
	product_code varchar(50) FOREIGN KEY REFERENCES tblproducts(product_code) ON DELETE CASCADE
);<pre>

What I have tried:

<pre>;WITH sumStocksIn AS (
	SELECT product_code, stocksin_size, SUM(stocksin_stocks) 'TotalStocks' FROM tblproducts_stocksin WHERE stocksin_status='STOCK IN'
		GROUP BY product_code, stocksin_size
) 
, sumStocksOut AS (
	SELECT product_code, stocksout_size, SUM(stocksout_qty) 'TotalOrder' FROM tblproducts_stocksout
		GROUP BY product_code, stocksout_size
) SELECT s.product_code, p.product_name, p.product_supplier, ISNULL(stocksin_size, '') 'Size',  s.TotalStocks - ISNULL(o.TotalOrder, 0) 'Stocks' 
	FROM sumStocksIn s
	LEFT JOIN sumStocksOut o
	ON s.product_code = o.product_code
	JOIN tblproducts p
	ON s.product_code = p.product_code;<pre>

John Th 16-Mar-18 15:06pm

Sample output:

Product Code    Name     Supplier      Size      Stocks
P-0001               Ninja      Nike         5          10
P-0002               Ninja1     Nike<pre>

Bryian Tan 16-Mar-18 17:42pm

How that possible size is null since it is a required field? You can provide some sample data for each table.

John Th 17-Mar-18 4:27am

What should I do? I want like this: https://ibb.co/fK8Bnx

The value of 'Stocks' column in datagridview should be minus in the tblstocksout.

I've tried it, but it only retrieve the products who have stocks and size.

Sample output:

https://ibb.co/ccioZc

As you can see, the value of 'Stocks' column was changed to 24. How can I include the products who has no stocks and size? Thanks!

John Th 17-Mar-18 5:01am

DECLARE @tblproducts TABLE  (
	product_code varchar(50) NOT NULL PRIMARY KEY,
	product_name varchar(100) NOT NULL,
	product_supplier varchar(50) NOT NULL,
	product_price int NOT NULL
)

INSERT INTO @tblproducts
	SELECT 'P-0001', 'LeBron 15 Ashes', 'Nike', 1500 UNION
	SELECT 'P-0002', 'KD 10 AS', 'Nike', 1500 UNION
	SELECT 'P-0003', 'Harden 2.0 Ignite', 'Adidas' 1800

DECLARE @tblorder TABLE (
	order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	order_qty int NOT NULL,
	order_size varchar(30) NOT NULL,
	order_status varchar(50) NOT NULL,
	order_Date varchar(100) NOT NULL,
	product_code varchar(50) FOREIGN KEY REFERENCES tblproducts(product_code) NOT NULL
)

DECLARE @tblstocks TABLE (
	stocks_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	stocksin_stocks int NOT NULL,
	stocksin_size int NOT NULL,
	stocksin_status varchar(50) NOT NULL,
	stocksin_Date varchar(100) NOT NULL,
	stocksin_Month varchar(100) NOT NULL,
	stocksin_Year varchar(100) NOT NULL,
	product_code varchar(50) FOREIGN KEY REFERENCES tblproducts(product_code) ON DELETE CASCADE
)

INSERT INTO @tblstocks
	SELECT 20,5,'STOCK IN', '1/1/2018','Jan',2018,'P-0001' UNION
	SELECT 5,5,'STOCK IN', '1/3/2018','Jan',2018,'P-0001' UNION
	SELECT 15,6,'STOCK IN', '1/7/2018','Jan',2018,'P-0001' UNION
	SELECT 5,6,'STOCK IN', '1/7/2018','Jan',2018,'P-0001'
	

INSERT INTO @tblorder
--	SELECT 1,5, 'STOCK OUT', '1/3/2018','P-0001'

;WITH sumStocksIn AS (
	SELECT product_code, stocksin_size, SUM(stocksin_stocks) 'TotalStocks' FROM tblproducts_stocksin WHERE stocksin_status='STOCK IN'
		GROUP BY product_code, stocksin_size
) , sumStocksOut AS (
	SELECT product_code, stocksout_size, SUM(stocksout_qty) 'TotalOrder' FROM tblproducts_stocksout
		GROUP BY product_code, stocksout_size) 
		
	SELECT s.product_code, p.product_name, p.product_supplier, s.stocksin_size, s.TotalStocks - ISNULL(o.TotalOrder, 0) 'Stocks' 
	FROM sumStocksIn s
	LEFT JOIN sumStocksOut o
	ON s.product_code = o.product_code
	JOIN tblproducts p
	ON s.product_code = p.product_code;

John Th 17-Mar-18 5:35am

Help me, please. @BryianTan

Bryian Tan 17-Mar-18 11:31am

Then the join need to start from tblproducts table. Here is an example.

DECLARE @tblproducts TABLE  (
	product_code varchar(50) NOT NULL PRIMARY KEY,
	product_name varchar(100) NOT NULL,
	product_supplier varchar(50) NOT NULL,
	product_price int NOT NULL
)

INSERT INTO @tblproducts
	SELECT 'P-0001', 'LeBron 15 Ashes', 'Nike', 1500 UNION
	SELECT 'P-0002', 'KD 10 AS', 'Nike', 1500 UNION
	SELECT 'P-0003', 'Harden 2.0 Ignite', 'Adidas', 1800

DECLARE @tblorder TABLE (
	order_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	stocksout_qty int NOT NULL,
	stocksout_size varchar(30) NOT NULL,
	order_status varchar(50) NOT NULL,
	order_Date varchar(100) NOT NULL,
	product_code varchar(50) 
)

DECLARE @tblstocks TABLE (
	stocks_id int NOT NULL IDENTITY(1,1) PRIMARY KEY,
	stocksin_stocks int NOT NULL,
	stocksin_size int NOT NULL,
	stocksin_status varchar(50) NOT NULL,
	stocksin_Date varchar(100) NOT NULL,
	stocksin_Month varchar(100) NOT NULL,
	stocksin_Year varchar(100) NOT NULL,
	product_code varchar(50) 
)

INSERT INTO @tblstocks
	SELECT 20,5,'STOCK IN', '1/1/2018','Jan',2018,'P-0001' UNION
	SELECT 5,5,'STOCK IN', '1/3/2018','Jan',2018,'P-0001' UNION
	SELECT 15,6,'STOCK IN', '1/7/2018','Jan',2018,'P-0001' UNION
	SELECT 5,6,'STOCK IN', '1/7/2018','Jan',2018,'P-0001'
	

INSERT INTO @tblorder
	SELECT 1,5, 'STOCK OUT', '1/3/2018','P-0001'

;WITH sumStocksIn AS (
	SELECT product_code, stocksin_size, SUM(stocksin_stocks) 'TotalStocks' FROM @tblstocks WHERE stocksin_status='STOCK IN'
		GROUP BY product_code, stocksin_size
) , sumStocksOut AS (
	SELECT product_code, stocksout_size, SUM(stocksout_qty) 'TotalOrder' FROM @tblorder
		GROUP BY product_code, stocksout_size) 
SELECT 
	p.product_code, p.product_name, p.product_supplier, ISNULL(s.stocksin_size,0) 'Size', 
	ISNULL(s.TotalStocks,0) - ISNULL(o.TotalOrder, 0) 'Stocks' 
	FROM @tblproducts p
	LEFT JOIN sumStocksOut o ON p.product_code = o.product_code
	LEFT JOIN sumStocksIn s ON o.product_code = s.product_code

John Th 17-Mar-18 22:34pm

Thanks to you @BryianTan! :)

How to subtract two colums from different tables

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

How to subtract two colums from different tables

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us