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