Count Double when SUM Quantity

Question

HI, May i know why  i  use sum the QTY, it will count double ? kindly advise, thank you 

Example ,if SUM 20 unit, it  will become 40 unit.

SELECT 
       A.INV_ID
      ,B.INV_TYPE
      ,B.INV_SHORTDESC
      ,D.INV_REASON_TYPE
      ,SUM(A.INV_TRANS_QTY) AS SUM_QTY
     
  FROM OTH_ENG_TRANSACTION A
  JOIN OTH_ENG_DETAILS B ON A.INV_ID=B.INV_ID
  JOIN OTH_INV_USER C ON C.INV_EMP_ID = A.INV_CREATE_USER
  JOIN OTH_INV_REASON D ON A.INV_REASON_ID = D.INV_REASON_ID
  WHERE 1=1


and a.INV_ID = 'PW.K3103.021'


  Group by 

      A.INV_ID
      ,B.INV_TYPE
      ,B.INV_SHORTDESC
      ,D.INV_REASON_TYPE

ORDER BY 

A.INV_ID
      ,B.INV_TYPE
      ,B.INV_SHORTDESC
      ,D.INV_REASON_TYPE

Answer 1

Probably you have double in the detail query. Remove the GROUP BY and see what you have.

Check your JOINs and WHERE conditions.

Investigate if DISTINCT appropriate.

---

Kalman Toth SQL 2008 GRAND SLAM
Paperback: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 2

(Example ,if SUM 20 unit, it  will become 40 unit. )I believe you

but if you want us to suggest  you something useful please provide sample data to reproduce the problem

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance

Answer 3

Hi ， the result still the same 

Answer 4

Group by Trans_ID not INV_ID....

Answer 5

Check

SELECT        A.INV_ID
      , (select count(*) from   OTH_ENG_DETAILS B where A.INV_ID=B.INV_ID ) bCount
      , A.INV_CREATE_USER, (select count(*) from  OTH_INV_USER C where C.INV_EMP_ID = A.INV_CREATE_USER) cCount
      , A.INV_REASON_ID, (select count(*) from  OTH_INV_REASON D where A.INV_REASON_ID = D.INV_REASON_ID dCount
  FROM OTH_ENG_TRANSACTION A
  WHERE 1=1  
    and a.INV_ID = 'PW.K3103.021'

to see which join causes doubles.

---

Serg

Answer 6

You can try to run the query without the GROUP BY specified. Most likely you'll get duplicate rows because one of your joins have duplicate rows or causes duplicate rows and that you can see if you run a regular quewry without the GROUP BY. The duplicate rows could then either be an actual duplicate row or just that the JOIN you do isn't making it distinct in your query.

I can also see that you have a join on the OTH_INV_USER table, but it doesn't seems like you are using anything from this table. If you don't plan to use it later, I'd sugest you to remove from the query.

---

Steen Schlüter Persson (DK)

Answer 7

You have too many joins in your query so no surprise you're running into wrong amounts. I have a blog post explaining this exact problem and suggesting solutions, please, take a look

Aggregates with multiple tables

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog