title: Missing on data with Group by and Sum
body: <p>I'm trying to make sense of the group by clause in a specific case: I joined two tables that are supposed to have the same dollar amount for each invoice, so I'm trying to make a Query to confirm there is no discrepancy. Here is the tables, query and result:</p>
  
  <pre><code>Table A:
  Order_Number | Order_Number_Line | Amount
  1            | 1                 | 80
  1            | 2                 | 20
  1            | 3                 | 30
  1            | 4                 | 70
  
  Table B
  
  Order_Number | Order_Number_Line | Invoice_ID | Invoice_Line_Number | Amount
  1            | 1                 | 1234       | 1                   | 20
  1            | 2                 | 1234       | 2                   | 5
  1            | 3                 | 1234       | 3                   | 10
  1            | 4                 | 1234       | 4                   | 25
  1            | 1                 | 1234       | 1                   | 35
  1            | 2                 | 1234       | 2                   | 7
  1            | 3                 | 1234       | 3                   | 15
  1            | 4                 | 1234       | 4                   | 10
  1            | 1                 | 1234       | 1                   | 25
  1            | 2                 | 1234       | 2                   | 8
  1            | 3                 | 1234       | 3                   | 5
  1            | 4                 | 1234       | 4                   | 35
  
  Select A.Order_Number, A.Amount, B.Amount
  From Table_A as A
  Left Join (Select Order_Number, Amount From Table_B) as B
  On A.Order_Number = B.Order_Number
  
  Order_Number  |  Amount_Table_A  |  Amount_Table_B 
  01            |  80              |   20            
  01            |  80              |   5            
  01            |  80              |   10            
  01            |  80              |   25            
  01            |  80              |   35            
  01            |  80              |   7            
  01            |  80              |   15           
  01            |  80              |   10         
  01            |  80              |   25            
  01            |  80              |   8          
  01            |  80              |   5           
  01            |  80              |   35
  01            |  20              |   20            
  01            |  20              |   5            
  01            |  20              |   10            
  01            |  20              |   25            
  01            |  20              |   35            
  01            |  20              |   7            
  01            |  20              |   15           
  01            |  20              |   10         
  01            |  20              |   25            
  01            |  20              |   8          
  01            |  20              |   5           
  01            |  20              |   35
  01            |  30              |   20            
  01            |  30              |   5            
  01            |  30              |   10            
  01            |  30              |   25            
  01            |  30              |   35            
  01            |  30              |   7            
  01            |  30              |   15           
  01            |  30              |   10         
  01            |  30              |   25            
  01            |  30              |   8          
  01            |  30              |   5           
  01            |  30              |   35
  01            |  70              |   20            
  01            |  70              |   5            
  01            |  70              |   10            
  01            |  70              |   25            
  01            |  70              |   35            
  01            |  70              |   7            
  01            |  70              |   15           
  01            |  70              |   10         
  01            |  70              |   25            
  01            |  70              |   8          
  01            |  70              |   5           
  01            |  70              |   35            
  </code></pre>
  
  <p>I tried using group by, but only got the first amount record for each table:</p>
  
  <pre><code>Select A.Order_Number, A.Amount, B.Amount
  From Table_A as A
  Left Join (Select Order_Number, Amount From Table_B) as B
  On A.Order_Number = B.Order_Number
  Group By A.Order_Number
  
   Invoice_ID  |  Amount_Table_A  |  Amount_Table_B  |
   01          |  80              |  20              |
  </code></pre>
  
  <p>I tried adding the sum() clause to each amount attribute in the select statement and got the sum of each repeated record:</p>
  
  <pre><code>Select A.Order_Number, sum(A.Amount), sum(B.Amount)
  From Table_A as A
  Left Join (Select Order_Number, Amount From Table_B) as B
  On A.Order_Number = B.Order_Number
  Group By A.Order_Number
  
   Invoice_ID  |  Amount_Table_A  |  Amount_Table_B  |
   01          |  2400            |  800             |
  </code></pre>
  
  <p>how may I modify this query to show the real value?? Expected value is:</p>
  
  <pre><code> Invoice_ID  |  Amount_Table_A  |  Amount_Table_B  |
   01          |  200             |  200             |
  </code></pre>
  
  <p>Please keep in mind that this is an over simplified data set. The real table has over a million records and I need to make sure that the Invoice_ID adds up to the same total in both tables.</p>
  
  <p>Thank you so much!!!</p>