title: Sum a one to many based on multiple criteria and JOINS
body: <p>I'm not sure how to word my question. But I have 2 different tables that need to join together to give results.</p>
  
  <pre><code>Table A
  --------------------------
  Id  |  Field_Id |  Value
  --------------------------
  1234|     16    |   PO456
  1234|      7    |   40
  1334|     16    |   PO456
  1334|      7    |   20
  1246|     16    |   PO679
  1246|      7    |   100
  1247|     16    |   PO679
  1247|      7    |   20
  1247|     16    |   PO679
  1247|      7    |   40
  
  
  Table B
  --------------
  Id  |  Hours | 
  --------------
  1234|   2
  1234|   3
  1234|   4
  1234|   5
  1334|   8
  1334|   8
  1334|   8
  1246|   3
  1246|   3
  1247|   8
  1247|   8
  </code></pre>
  
  <p>Now the Result I want should look like this.</p>
  
  <pre><code>Result
  -----------------------
  Value  |  Est |  Hours
  -----------------------
  PO456  |   60 |   38
  PO679  |   160|   22
  </code></pre>
  
  <p>Here is my sql:</p>
  
  <pre><code>select a.value as "Id", CAST(sum(d.value) AS Decimal(10,2)) as 'Est' 
  , COALESCE(sum(b.Hours),0) as "Hours"
  
  from table_a a 
  left Join table_b b ON a.Id = b.Id
  inner join table_a d ON d.Id = a.Id AND d.Field_Id = '7'
  where a.Field_Id =  '16' and a.value &lt;&gt; ' ' 
  Group By a.value
  ORDER BY a.value  ASC
  </code></pre>
  
  <p>Now my hours column comes in correctly, however it's the Est column that comes back with strange data, like the numbers are thousands off. Is it not being tied back to the first column somehow? I basically want to get a sum of all Id's with that same a.value on one line. My work around right now is it displays all Id's and there are simply multiple Values and it's broken down individually. However, I want it condensed. Any assistance would be appreciated. If there are minor spelling errors in my code (this isn't the actual code, the code is simply renamed privacy reasons, I need help on the logic). Please and thank you.</p>