How to Get Totals from two tables? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to Get Totals from two tables?

Author

Topic

dohamsg
Starting Member

22 Posts

Posted - 2010-12-04 : 06:39:38

Hi,
[SQLServer 2008]

My report should show:
Description, QuantityPurchased (total qty), QuantityRepair (total qty).

From tables:
Product, PurchaseDetail (input), RepairDetail (output).

Any idea how to produce such a query?

Thanks.

NB.
I've tried the following query; but it shows only the Items that exist in both tables (PurchaseDetail and RepairDetail):


SELECT
	Product.Description, 
	Purchase.Quant As QuantityPurchased, 
	Repair.Quant AS QuantityRepair, 
	(Purchase.Quant - Repair.Quant) AS Balance

FROM (
	SELECT
		ProductID, 
		SUM(Qty) AS Quant 
	FROM PurchaseDetail
	GROUP BY ProductID
	) AS Purchase
	
	FULL OUTER JOIN
	
	(SELECT 
			ProductID, 
			SUM(Qty) AS Quant 
	FROM RepairDetail 
	GROUP BY ProductID
	) As Repair
		ON Purchase.ProductID = Repair.ProductID
		INNER JOIN Product ON Purchase.ProductID = Product.ProductID

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 07:04:06

Try this on your actual data
I could not test as I dont have sample data
---------------------------------------------


SELECT P.Description, QuantityPurchased, QuantityRepair, ( QuantityPurchased - QuantityRepair ) AS Balance
FROM Product P 
INNER JOIN 
(
SELECT 
Purchase.ProductID P_ProductID, Purchase.Quant QuantityPurchased , 
Repair.ProductID R_ProductID, Repair.Quant QuantityRepair 
FROM 	
	(
		SELECT
		ProductID, 
		SUM(Qty) AS Quant 
		FROM PurchaseDetail
		GROUP BY ProductID
	) AS Purchase
	FULL OUTER JOIN 
	(
		SELECT 
		ProductID, 
		SUM(Qty) AS Quant 
		FROM RepairDetail 
		GROUP BY ProductID
	) As Repair ON Purchase.ProductID = Repair.ProductID
) A ON P.ProductID = P_ProductID OR P.ProductID = R_ProductID

Vaibhav T

If I cant go back, I want to go fast...

dohamsg
Starting Member

22 Posts

Posted - 2010-12-04 : 08:27:44

99% OK; the only problem remaining is the NULL value, if one table (PurchaseDetail or RepairDetail) returns NULL value the balance shows NULL.

Is there any way to return zero instead of NULL when there is no corresponding record in the other table, so that the balance will calculate correctly?

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 08:31:28

See the red part
It will solve the problem.


SELECT P.Description, QuantityPurchased, QuantityRepair, ( QuantityPurchased - QuantityRepair ) AS Balance
FROM Product P 
INNER JOIN 
(
SELECT 
Purchase.ProductID P_ProductID, ISNULL(Purchase.Quant, 0) QuantityPurchased , 
Repair.ProductID R_ProductID, ISNULL(Repair.Quant, 0) QuantityRepair 
FROM 	
	(
		SELECT
		ProductID, 
		SUM(Qty) AS Quant 
		FROM PurchaseDetail
		GROUP BY ProductID
	) AS Purchase
	FULL OUTER JOIN 
	(
		SELECT 
		ProductID, 
		SUM(Qty) AS Quant 
		FROM RepairDetail 
		GROUP BY ProductID
	) As Repair ON Purchase.ProductID = Repair.ProductID
) A ON P.ProductID = P_ProductID OR P.ProductID = R_ProductID

Vaibhav T

If I cant go back, I want to go fast...

dohamsg
Starting Member

22 Posts

Posted - 2010-12-04 : 08:57:30

Awesome Thank you very much, you solved my problem.
SOLVED.

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-12-04 : 09:00:27

Welcome Glad that I could help you.

Vaibhav T

If I cant go back, I want to go fast...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources