Wondering if it’s possible to remove results with the same value.
I have a payments table containing ADJUSTMENTS and REFUNDS entered as AJUSTE and ESTORNO. While both can exist in an invoice, if the total sum of each are equal, they are to be ignored. If the sum of each are not equal, then will be considered for calculation.
I have come up with a list of each as follows:
select sum(pay_amount) as pay_amount, pay_type, pay_date, active, invoice_no, invoice_date
from payments
where pay_type = 'AJUSTE'
and pay_date like '%-%-2024'
and active = '1'
and pay_amount not in (
select SUM(pay_amount) as pay_amount
from payments
where pay_type = 'ESTORNO' and pay_date like '%-%-2024' and active = '1'
group by invoice_no
)
GROUP BY invoice_no;
Then this query for the opposite:
select sum(pay_amount) as pay_amount, pay_type, pay_date, active, invoice_no, invoice_date
from payments
where pay_type = 'ESTORNO'
and pay_date like '%-%-2024'
and active = '1'
and pay_amount not in (
select SUM(pay_amount) as pay_amount
from payments
where pay_type = 'AJUSTE' and pay_date like '%-%-2024' and active = '1'
group by invoice_no
)
GROUP BY invoice_no;
Although refunds are negative, I want to exclude all amounts that are the same and add the ones that are different.
Is this possible through a query in MySQL 5.7?
—- edit ——
I have created a fiddle with demo data: https://www.mycompiler.io/view/6qxcOxSlkuv
AJUSTE (Adjustements) are positive number in the DB meanwhile ESTORNO (refunds) are negative amounts.
When a refund is applied, an adjustment must follow in the same amount to zero out the balance on an invoice. There are scenarios where an adjustment can exist without a refund when the client doesn’t pay the full balance, correct an error, etc. In this case, we need to know the pay_amount and pay_type of this adjustment.
It is also possible that there may be 2 adjustments and only 1 refund. Ex. Client paid half the balance and a refund of that half payment was applied, plus a full adjustment to write-off the invoice.
The query must look at each individual invoice. When the sum of all refunds and adjustments within the same invoice exists and adds up to zero, we skip over that particular invoice. If the sum of adjustment is more than refund or vice versa, we want the pay_type (AJUSTE or ESTORNO) with the difference (pay_type). (ex. ESTORNO 100, AJUSTE 500. Must output AJUSTE 400).
The query you provided in the answer returns invoices 78 and 79.
Invoice 79 has ESTORNO 100 and AJUSTE 551.20 which is correct.
Invoice 78 has ESTORNO 100 and AJUSTE 200 which is also correct.
Invoice 55 has AJUSTE 2000 and doesn’t show up on the results.
Hope this all makes sense.
— second edit —–
If the sum() of AJUSTE and ESTORNO in an invoice_no is the same, it gets ignored – do not include in results.
If the sum() of either AJUSTE or ESTORNO in an invoice_no is different, show both results as a positive number.
Payment_id is the primary key. My apologies as the mysql fiddle didn’t include it.
Expected result from mysql fiddle:
2
Answers
Join your subqueries and show only the rows that have different sums for the same invoice.
DEMO
To avoid
ONLY_FULL_GROUP_BY
errors you have to remove thepay_date
,active
, andinvoice_date
columns from theSELECT
list, since they may not be unique within the groups.See another example
Condition
When the sum of all refunds and adjustments within the same invoice exists and adds up to zero, we skip over that particular invoice.
So first step – Calculation sum for pay_type in (‘AJUSTE’,’ESTORNO’) for every invoice_no.
We take invoices, where total sum is not <>0.
Subquery.
Full query.
Inner join previous output with payments table
fiddle