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

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.

The query ”SELECT INVOICE_ID FROM ORDERS GROUP BY INVOICE_ID

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