mysql subquery to sum like checknumbers

Question

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:

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) > 1) AS a) AND concatckBatch != '')

gives me this answer:

id  checkNumber amount
15  1114392      1708.5
16  1114392      1432.2
17  1114392      1188.06
18  1114392      1405.25
19  1114392      603.96

I need it to give me this answer, which is the sum of all the identical check numbers and the corresponding id:

id  checkNumber amount
15  1114392     6337.97
16  1114392     6337.97
17  1114392     6337.97
18  1114392     6337.97
19  1114392     6337.97

I've tried various subqueries but for some reason I'm stymied.

EDITED: this is the correct answer thanks to jpw:

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) > 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) > 1
          ) AS a
       ) AND concatckBatch != ''
    )

jpw · Accepted Answer · 2015-03-15T00:00:55.040

You could do the calculation in a derived table. The query should be self-explanatory.

Select
    i.id,
    i.CheckNumber,
    i2.AmountPaid
from
    HS i 
join (
    select 
       checknumber, 
       sum(amountpaid) AmountPaid 
    from HS 
    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) > 1
          ) AS a
       ) AND concatckBatch != ''
    )

In other databases this could easily have been achieved using a windowed analytical function, like sum(i.amountpaid) over (partition by i.checknumber) but sadly MySQL doesn't support that construct.

mysql subquery to sum like checknumbers

1 Answers1