MySQL remove duplicate distinct values

Ronaldo
September 5, 2024
144 views
0 votes
2 Answers

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:

Tags: mysql

Answers

Join your subqueries and show only the rows that have different sums for the same invoice.

SELECT e.*
FROM (
    select sum(pay_amount) as pay_amount, pay_type, invoice_no
    from payments 
    where pay_type = 'ESTORNO' and pay_date like '%-%-2024' and active = '1' 
    group by invoice_no, pay_type
) AS e
JOIN (
    select sum(pay_amount) as pay_amount, pay_type, invoice_no
    from payments 
    where pay_type = 'AJUSTE' and pay_date like '%-%-2024' and active = '1' 
    group by invoice_no, pay_type
) AS a ON a.invoice_no = e.invoice_no AND a.pay_amount != e.pay_amount

UNION ALL

SELECT a.*
FROM (
    select sum(pay_amount) as pay_amount, pay_type, invoice_no
    from payments 
    where pay_type = 'ESTORNO' and pay_date like '%-%-2024' and active = '1' 
    group by invoice_no, pay_type
) AS e
JOIN (
    select sum(pay_amount) as pay_amount, pay_type, invoice_no
    from payments 
    where pay_type = 'AJUSTE' and pay_date like '%-%-2024' and active = '1' 
    group by invoice_no, pay_type
) AS a ON a.invoice_no = e.invoice_no AND a.pay_amount != e.pay_amount

DEMO

To avoid ONLY_FULL_GROUP_BY errors you have to remove the pay_date, active, and invoice_date columns from the SELECT list, since they may not be unique within the groups.

- ValNik
- September 5, 2024 at 6:41 pm
- 0 votes
0
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.
```
select invoice_no
  ,sum(pay_amount) sum_AE
from payments p
where pay_type in('AJUSTE','ESTORNO') and pay_date like '%-%-2024' 
     and active = '1'
group by invoice_no
having abs(sum(pay_amount))>0.001
```
invoice_no sum_AE

55 2000

78 100

79 451.20000000000005

Full query.
Inner join previous output with payments table
```
select p.invoice_no,abs(pay_amount) pay_amount,pay_type
from payments p
inner join(
   select invoice_no 
     ,sum(pay_amount) sum_AE
   from payments p
   where pay_type in('AJUSTE','ESTORNO') and pay_date like '%-%-2024' 
       and active = '1'
   group by invoice_no
   having abs(sum(pay_amount))>0.001
 )c on p.invoice_no=c.invoice_no
where pay_type in('AJUSTE','ESTORNO')
order by p.invoice_no ;
```
invoice_no pay_amount pay_type

55 2000 AJUSTE

78 200 AJUSTE

78 100 ESTORNO

79 551.2 AJUSTE

79 100 ESTORNO

fiddle
Login or Signup to reply.