Click here to Skip to main content
15,881,687 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have 3 tables Company, Purchase and Sales.
CREATE table tblCompany (CompanyID int, Company varchar(50))

INSERT INTO tblCompany (CompanyID, Company) VALUES (1, 'ABC')

CREATE table tblPurchases (TranID int, CompanyID int, PurQty int)

INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (1, 1, 774)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (2, 1, 351)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (3, 1, 600)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (4, 1, 500)
INSERT INTO tblPurchases (TranID, CompanyID, PurQty) VALUES (5, 1, 501)

CREATE table tblSales (TranID int, CompanyID int, SaleQty int)

INSERT INTO tblSales (TranID, CompanyID, SaleQty) VALUES (1, 1, 2726)

I want correct balance of quantities.

CompanyID, TPurQty, TSaleQty, BalanceQty
1 , 2726 , 2726 , 0

But It displays like this:
1 , 2726 ,13630 ,-10904

Where is the error in query?

What I have tried:

SELECT C.CompanyID, ISNULL(SUM(P.PurQty),0) TPurQty, ISNULL(SUM(S.SaleQty),0) TSaleQty
	, (ISNULL(SUM(P.PurQty),0) - ISNULL(SUM(S.SaleQty),0)) BalanceQty   FROM tblCompany C
	FULL OUTER JOIN tblPurchases P ON C.CompanyID = P.CompanyID
		FULL OUTER JOIN tblSales S ON P.CompanyID = S.CompanyID
GROUP by C.CompanyID
Posted
Updated 21-Aug-18 23:53pm
v2
Comments
Santosh kumar Pithani 17-Aug-18 4:55am    
AS i know single value (2726) is repeating i.e 1..n rows further your doing sum on it like (2726 * N rows in table).i suggest you to use "sum(distinct columnname)"
Santosh kumar Pithani 17-Aug-18 5:22am    
what conditions?

SQL
SELECT  C.CompanyID
       ,P.PurQty             AS TPurQty
       ,S.SaleQty            AS TSaleQty
       ,P.PurQty- S.SaleQty  AS BalanceQty  

      FROM tblCompany C
             inner JOIN (
     select CompanyID,
            sum(ISNULL(PurQty,0)) as PurQty 
      FROM tblPurchases GROUP BY CompanyID
                        )AS P ON C.CompanyID = P.CompanyID

	     inner JOIN (
    select CompanyID,
        SUM(ISNULL(SaleQty,0)) AS SaleQty
   FROM tblSales GROUP BY CompanyID 
                       ) AS S  ON P.CompanyID = S.CompanyID
 
Share this answer
 
v2
Comments
sulomila 17-Aug-18 5:25am    
thank you.
Santosh kumar Pithani 17-Aug-18 5:26am    
:-)
SELECT C.CompanyID, ISNULL(SUM(P.PurQty),0) TPurQty, ISNULL(S.SaleQty,0) TSaleQty
	, (ISNULL(SUM(P.PurQty),0) - ISNULL(S.SaleQty,0)) BalanceQty   FROM #tblCompany C
	FULL OUTER JOIN #tblPurchases P ON C.CompanyID = P.CompanyID
		FULL OUTER JOIN #tblSales S ON P.CompanyID = S.CompanyID
GROUP by C.CompanyID,s.SaleQty
 
Share this answer
 
Comments
sulomila 17-Aug-18 4:56am    
No Change. Same Results.
But my result is correct.
can u tell me which Senior you follow
 
Share this answer
 
Comments
CHill60 22-Aug-18 8:35am    
Don't post comments or questions as solutions - use the "Have a question or comment?" link next to a post or the "Reply" link next to a comment

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900