Way to show records that are not unique in PLSQL

Question

I have imdb_id in movies table. Total number of records is 10856. But the following query shows that there are 10846 unique ids.

select count(imdb_id) from movies;

How can I display only those that are not unique? I think I need to count = 0; But what is the good logic?

Here is my table movies

CREATE TABLE "AS6400U"."MOVIES" 
(   "ID" NUMBER(38,0), 
    "IMDB_ID" VARCHAR2(26 BYTE), 
    "POPULARITY" NUMBER(38,6), 
    "BUDGET" NUMBER(38,0), 
    "REVENUE" NUMBER(38,0), 
    "ORIGINAL_TITLE" VARCHAR2(500 BYTE), 
    "CAST" VARCHAR2(500 BYTE), 
    "HOMEPAGE" VARCHAR2(300 BYTE), 
    "DIRECTOR" VARCHAR2(600 BYTE), 
    "TAGLINE" VARCHAR2(400 BYTE), 
    "KEYWORDS" VARCHAR2(500 BYTE), 
    "OVERVIEW" VARCHAR2(2000 BYTE), 
    "RUNTIME" NUMBER(38,0), 
    "GENRES" VARCHAR2(400 BYTE), 
    "PRODUCTION_COMPANIES" VARCHAR2(500 BYTE), 
    "RELEASE_DATE" DATE, 
    "VOTE_COUNT" NUMBER(38,0), 
    "VOTE_AVERAGE" NUMBER(38,1), 
    "RELEASE_YEAR" NUMBER(38,0), 
    "BUDGET_ADJ" NUMBER(38,3), 
    "REVENUE_ADJ" NUMBER(38,5)
   );

Kind regards, Anna

Does this answer your question? [How do I find duplicate values in a table in Oracle?](https://stackoverflow.com/questions/59232/how-do-i-find-duplicate-values-in-a-table-in-oracle) — Abra, Oct 20 '20 at 18:42

score 1 · Answer 1 · answered Oct 20 '20 at 19:09

1

Actually select count(imdb_id) from movies; tells you there are ten records where IMDB_ID is null. count() only counts non-null values. To find how many of those are duplicates you need to run:

select count(distinct imdb_id) from movies;

answered Oct 20 '20 at 19:09

APC

137,061
19
153
266

score 0 · Answer 2 · answered Oct 20 '20 at 19:01

0

These are duplicates:

select imdb_id
from movies
group by imdb_id
having count(*) > 1;

To see all data about them, use it as an inline view:

select *
from movies
where imdb_id in (select imdb_id
                  from movies
                  group by imdb_id
                  having count(*) > 1
                 );

answered Oct 20 '20 at 19:01

Littlefoot

78,293
10
26
46

A duplicate answer to a duplicate question. Please explain why this question is not a duplicate. Please explain why your answer is different to the answer in the duplicate question. – Abra Oct 20 '20 at 19:19

score 0 · Answer 3 · answered Oct 20 '20 at 19:22

You can also may use analytical functions, like this:

create table emp as select * from  hr.employees;
insert into emp select * from hr.employees where department_id = 10;

select * from (
        select  emp.*, count(*) over(partition by employee_id order  by hire_date) cnt from emp )
where cnt = 2 ;

For your data it may look like:

select * from (
        select  m.*, count(*) over(partition by imdb_id order  by id) cnt from  movies m)
where cnt = 2 ;

Way to show records that are not unique in PLSQL

3 Answers3