SELECT
SUM(amt) AS total_sum
FROM (
SELECT
payment.PayDate AS DatePay,
MAX(CONCAT(patient.LName, ', ', patient.FName, ' ', patient.MiddleI)) AS lfname,
GROUP_CONCAT(DISTINCT provider.Abbr) AS ProviderAbbr,
payment.CheckNum,
COALESCE(SUM(paysplit.SplitAmt), 0) AS amt,
payment.PayNum,
definition.ItemName,
payment.PayType
FROM
payment
LEFT JOIN paysplit ON payment.PayNum = paysplit.PayNum
LEFT JOIN patient ON payment.PatNum = patient.PatNum
LEFT JOIN provider ON paysplit.ProvNum = provider.ProvNum
LEFT JOIN definition ON payment.PayType = definition.DefNum
WHERE
payment.PayDate BETWEEN '2024-01-01' AND '2024-01-05'
AND payment.PayType IN (70, 71)
AND paysplit.UnearnedType NOT IN (301, 325)
GROUP BY
payment.PayNum,
payment.PayDate,
payment.CheckNum,
definition.ItemName,
payment.PayType,
provider.ProvNum
) AS subquery;