Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Count Double when SUM Quantity

Answered Count Double when SUM Quantity

  • Monday, January 14, 2013 7:41 AM
     
      Has Code
    Sign In to Vote
    0

    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

     

All Replies

  • Monday, January 14, 2013 7:44 AM
    Moderator
     
     
    Sign In to Vote
    0

    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


     
  • Monday, January 14, 2013 8:07 AM
    Answerer
     
     
    Sign In to Vote
    0

    (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

     
  • Monday, January 14, 2013 8:20 AM
     
     
    Sign In to Vote
    0

    Hi ， the result still the same 

     
  • Monday, January 14, 2013 10:43 AM
     
     
    Sign In to Vote
    0
    Group by Trans_ID not INV_ID....
     
  • Monday, January 14, 2013 12:06 PM
     
     Answered Has Code
    Sign In to Vote
    0

    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

     
  • Monday, January 14, 2013 12:14 PM
     
     Answered
    Sign In to Vote
    0

    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)

     
  • Monday, January 14, 2013 12:47 PM
    Moderator
     
     
    Sign In to Vote
    0

    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