title: mysql subquery to sum like checknumbers
body: <p>I have two tables, one with id and concatenations of fields I'm checking for duplicates, and another with id and all other assorted data, one item being AmountPaid. The below query:</p>
  
  <pre><code>Select
  i.id,
  i.CheckNumber,
  AmountPaid
  from
  HS i where i.id in (
  SELECT id
  FROM HS_dups
  WHERE concatckBatch IN (SELECT *
        FROM (SELECT concatckBatch
        FROM HS_dups
        GROUP BY concatckBatch
        HAVING COUNT(concatckBatch) &gt; 1) AS a) AND concatckBatch != '')
  </code></pre>
  
  <p>gives me this answer:</p>
  
  <pre><code>id  checkNumber amount
  15  1114392      1708.5
  16  1114392      1432.2
  17  1114392      1188.06
  18  1114392      1405.25
  19  1114392      603.96
  </code></pre>
  
  <p>I need it to give me this answer, which is the sum of all the identical check numbers and the corresponding id:</p>
  
  <pre><code>id  checkNumber amount
  15  1114392     6337.97
  16  1114392     6337.97
  17  1114392     6337.97
  18  1114392     6337.97
  19  1114392     6337.97
  </code></pre>
  
  <p>I've tried various subqueries but for some reason I'm stymied.</p>
  
  <p>EDITED: this is the correct answer thanks to jpw:</p>
  
  <pre><code>Select
      i.id,
      i.CheckNumber,
      i2.AmountPaid
  from
  HS i join (
      select 
         checknumber, 
         sum(amountpaid) AmountPaid 
      from HS 
  where id in (
         SELECT id
         FROM HS_dups
         WHERE concatckBatch IN (
            SELECT *
            FROM (
               SELECT concatckBatch
               FROM HS_dups
               GROUP BY concatckBatch
               HAVING COUNT(concatckBatch) &gt; 1
            ) AS a
         ) AND concatckBatch != ''
      )
      group by checknumber) i2 on i2.checkNumber = i.checkNumber
  where 
      i.id in (
         SELECT id
         FROM HS_dups
         WHERE concatckBatch IN (
            SELECT *
            FROM (
               SELECT concatckBatch
               FROM HS_dups
               GROUP BY concatckBatch
               HAVING COUNT(concatckBatch) &gt; 1
            ) AS a
         ) AND concatckBatch != ''
      )
  </code></pre>