x

I just want the sum of quantities and below does not give me an accurate result

SELECT  dbo.tbl_SubProductType.SubProductName, 
        SUM(dbo.tbl_Product.ProcurmentQuantity) AS ProcurmentQuantity,
        SUM(dbo.tbl_Product.InHandQuantity) AS InHandQuantity, 
        SUM(dbo.tbl_DetailIssue.RetainQuantity) AS RetainQuantity
FROM    dbo.tbl_DetailIssue 
     INNER JOIN dbo.tbl_Product ON dbo.tbl_DetailIssue.ProductID = dbo.tbl_Product.ProductID 
     INNER JOIN dbo.tbl_SubProductType ON dbo.tbl_Product.SubProductID = dbo.tbl_SubProductType.SubProductID
WHERE     (dbo.tbl_Product.IsActive = 1)
GROUP BY dbo.tbl_SubProductType.SubProductName
more ▼

asked Mar 19 '12 at 08:57 AM in Default

Kamran gravatar image

Kamran
1 1 1 2

Sorry, but too much information is missing here.

How is the query not fulfilling the requirements? As far as I can see you are SUMming quantites for each SubProductName - maybe the joins are causing issues - but I'm guessing here.

Please supply table scripts, sample data and expected results and we will be able to help.
Mar 19 '12 at 09:07 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The joins are probably duplicating up the base rows - either fix the join conditions or re-write the query to sum at the right level.

Try viewing the data without the sums/grouping and see where you have duplicate rows
more ▼

answered Mar 19 '12 at 09:05 AM

Kev Riley gravatar image

Kev Riley ♦♦
50.7k 43 49 76

(comments are locked)
10|1200 characters needed characters left

In addition to Kev Riley's suggestions, you must add dbo.tbl_Product.SubProductID in the GROUP BY clause. Otherwise, you could be grouping only on the SubProductName which may not be unique.

Moreover, you can try something like the following if the DetailIssue JOIN corresponds to duplicate rows

SELECT  dbo.tbl_SubProductType.SubProductName
,       SUM(dbo.tbl_Product.ProcurmentQuantity) AS ProcurmentQuantity
,       SUM(dbo.tbl_Product.InHandQuantity) AS InHandQuantity
,       RetainQuantity
FROM    dbo.tbl_Product
        INNER JOIN dbo.tbl_SubProductType
        ON dbo.tbl_Product.SubProductID = dbo.tbl_SubProductType.SubProductID
        INNER JOIN ( SELECT ProductID
                     ,      SUM(RetainQuantity) AS RetainQuantity
                     FROM   dbo.tbl_DetailIssue
                     GROUP BY ProductID
                   ) tbl_DetailIssue
        ON tbl_DetailIssue.ProductID = dbo.tbl_Product.ProductID
WHERE   ( dbo.tbl_Product.IsActive = 1 )
GROUP BY dbo.tbl_SubProductType.SubProductName
,       dbo.tbl_SubProductType.SubProductID
OR work on the same kind of pattern if vice versa.
more ▼

answered Mar 19 '12 at 09:53 AM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x341

asked: Mar 19 '12 at 08:57 AM

Seen: 616 times

Last Updated: Mar 19 '12 at 09:53 AM