I have a problem with left join, sum(), group by-CodePudding

I have 4 tables in the database

dbo.Product_Tbl
dbo.Buy_Details_Tbl
R_BuyDetails_Tbl
Sale_Details_Tbl
Re_Sale_Details_Tbl

They all have a one-to-many relationship with the first table. I am trying to get the sum of the quantities sold and bought, the sum total amount sold and bought, the sum quantities returned bought and sold, and the sum total amount returned bought and sold in the tables above The problem is that this sentence gives a wrong sum. Sometimes you double the sum to two or three times.

This is the sentence that I'm trying to find the true solution but it is wrong sum.

SELECT        dbo.Product_Tbl.Prd_ID, dbo.Product_Tbl.Prd_Code, dbo.Product_Tbl.Prd_Name, dbo.Cat_Tbl.CatName,
              dbo.Unit_Tbl.UnitName, sum(isnull(dbo.Buy_Details_Tbl.Qty,0))as 'sumBuyQty', sum(isnull(dbo.Buy_Details_Tbl.TotalAmount,0))as 'sumBuyTotalAmount', 
              sum(isnull(dbo.R_BuyDetails_Tbl.Qty,0)) AS  'sumR_BuyQty',sum(isnull(dbo.R_BuyDetails_Tbl.TotalAmount,0))as 'sumR_BuyTotalAmount',
              sum (isnull(dbo.Sale_Details_Tbl.Qty,0)) AS 'sumSaleQty',sum(isnull( dbo.Sale_Details_Tbl.TotalAmount,0)) AS'sumSaleTotalAmount',
              sum(isnull( dbo.Re_Sale_Details_Tbl.Qty,0)) As'sumRe_SaleQty',sum( isnull(dbo.Re_Sale_Details_Tbl.TotalAmount,0)) AS 'sumRe_SaleTotalAmount'
FROM            dbo.Product_Tbl left JOIN  dbo.Buy_Details_Tbl ON 
                       dbo.Product_Tbl.Prd_ID =   dbo.Buy_Details_Tbl.Prd_ID left JOIN
                         dbo.R_BuyDetails_Tbl ON 
                         dbo.Product_Tbl.Prd_ID = dbo.R_BuyDetails_Tbl.Prd_ID left JOIN
                         dbo.Re_Sale_Details_Tbl ON 
                         dbo.Product_Tbl.Prd_ID = dbo.Re_Sale_Details_Tbl.Prd_ID left JOIN
                         dbo.Sale_Details_Tbl ON
                          dbo.Product_Tbl.Prd_ID = dbo.Sale_Details_Tbl.Prd_ID left JOIN
                         dbo.Cat_Tbl ON 
                         dbo.Product_Tbl.Cat_ID = dbo.Cat_Tbl.Cat_ID left JOIN
                         dbo.Unit_Tbl ON 
                         dbo.Product_Tbl.Unit_ID = dbo.Unit_Tbl.Unit_ID group by dbo.Product_Tbl.Prd_ID, dbo.Product_Tbl.Prd_Code, dbo.Product_Tbl.Prd_Name, dbo.Cat_Tbl.CatName , dbo.Unit_Tbl.UnitName

CodePudding user response：

Rewriting your script using subqueries would look something like this:

    SELECT
    p.Prd_ID,
    p.Prd_Code,
    p.Prd_Name,
    c.CatName,
    u.UnitName,
    (SELECT SUM(bd.Qty) FROM dbo.Buy_Details_Tbl bd WHERE bd.Prd_ID = p.Prd_ID) AS sumBuyQty,
    (SELECT SUM(bd.TotalAmount) FROM dbo.Buy_Details_Tbl bd WHERE bd.Prd_ID = p.Prd_ID) AS sumBuyTotalAmount,
    (SELECT SUM(rbd.Qty) FROM dbo.R_BuyDetails_Tbl rbd WHERE rbd.Prd_ID = p.Prd_ID) AS sumR_BuyQty,
    (SELECT SUM(rbd.TotalAmount) FROM dbo.R_BuyDetails_Tbl rbd WHERE rbd.Prd_ID = p.Prd_ID) AS sumR_BuyTotalAmount,
    (SELECT SUM(sd.Qty) FROM dbo.Sale_Details_Tbl sd WHERE sd.Prd_ID = p.Prd_ID) AS sumSaleQty,
    (SELECT SUM(sd.TotalAmount) FROM dbo.Sale_Details_Tbl sd WHERE sd.Prd_ID = p.Prd_ID) AS sumSaleTotalAmount,
    (SELECT SUM(rsd.Qty) FROM dbo.Re_Sale_Details_Tbl rsd WHERE rsd.Prd_ID = p.Prd_ID) AS sumRe_SaleQty,
    (SELECT SUM(rsd.TotalAmount) FROM dbo.Re_Sale_Details_Tbl rsd WHERE rsd.Prd_ID = p.Prd_ID) AS sumRe_SaleTotalAmount
FROM dbo.Product_Tbl p
INNER JOIN dbo.Cat_Tbl c ON p.Cat_ID = c.Cat_ID
INNER JOIN dbo.Unit_Tbl u ON p.Unit_ID = u.Unit_ID

This query eliminates the multiple joins to the details tables.

The isnull function shouldn't be necessary since aggregrate functions such as SUM won't count nulls.