- Data Structure
- Networking
- RDBMS
- Operating System
- Java
- MS Excel
- iOS
- HTML
- CSS
- Android
- Python
- C Programming
- C++
- C#
- MongoDB
- MySQL
- Javascript
- PHP
- Physics
- Chemistry
- Biology
- Mathematics
- English
- Economics
- Psychology
- Social Studies
- Fashion Studies
- Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
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.
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 |
Advertisements