Search code examples
mysqlsubqueryrollup

mysql subquery to sum like checknumbers


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 != ''
    )

Solution

  • 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.