Write a DB2 query to find out all the duplicate INVOICE_ID in ORDERS DB2 table?

DB2DatabaseBig Data Analytics

The duplicate INVOICE_ID in ORDERS DB2 table can be found by using a combination of GROUP BY with HAVING clause and COUNT function. The GROUP BY will enable us to group the result invoice wise and COUNT function will enable us to count the number of occurrences in each group. We can use predicate in HAVING clause to filter the results for groups having count greater than one.

Below is the SQL query which we can use to find duplicate invoices in ORDERS DB2 table.

Example

SELECT INVOICE_ID FROM ORDERS
   GROUP BY INVOICE_ID
   HAVING COUNT(INVOICE_ID) > 1

For example, if we have ORDERS DB2 table as below.

ORDER_ID
INVOICE_ID
IRN22345
Z451189
IRN89767
Z451189
IRN67331
Z955189
IRN56902
Z225184
IRN99781
Z896671
IRN09863
Z225184

The query ”SELECT INVOICE_ID FROM ORDERS GROUP BY INVOICE_ID

HAVING COUNT(INVOICE_ID) > 1” will return the result below.

INVOICE_ID
Z451189
Z225184
raja
Published on 01-Dec-2020 04:50:46
Advertisements