Finding potential duplicates from Invoice table

sachin

Hi,
I have a Invoice table with five columns - Invoice ID, Invoice Number, Amount, date and Vendor. Invoice ID is PK.

I have to find the possible duplicates from this table.

Eg: 5 scenarios:
1.Same invoice number, Amount, date but vendor is slightly different
2.Same Amount, date, vendor but Invoice number is slightly different
3.Same Invoice number , date, vendor but Amount is slightly different
3.Same Invoice number , date, vendor but vendor is slightly different
5.All are same.

Can you please help me on how to implement these 5 scenarios and find the duplicates?

Bryan_H

HAVING clause will probably work here, e.g. for scenario 1:
SELECT invoice_number, amount, date, count(*) FROM invoices GROUP BY invoice_number, amount, date HAVING COUNT(*) > 1;
This will identify tuples of (invoice_number, amount, date) with more than 1 entry.
Doc: https://docs.vertica.com/12.0.x/en/sql-reference/statements/select/having-clause/