Count of duplicate rows in SQL

Question

I have a table student with only one column i.e. Roll No.
The values in the table are

10
20
10
10
20

There are in total 3 repetitive values i.e. 10 is repeated two extra times and 20 is repeated one extra time.

Output – 3

If you can’t answer the question, try to write down the approach in simple language

 

solved 6
TheDataMonk 55 years 62 Answers 2359 views Grand Master 4

Answers ( 62 )

    -1

    select sum(c-1) as reps from (select id,count(d.id) as c from test.dummy d group by d.id) x where c>1

  1. (Using MySQL as a language)

    Select COUNT(Roll No.) – COUNT(distinct Roll No.) as Count of duplicate Rows from student;

  2. select Roll no , count(Roll no) as Roll_no from student
    group by Roll no;

    1

    select sum(dup_cnt) as total_dup_rows from
    (select rollno, cnt-1 as dup_cnt from
    (select rollno, count(*) cnt from table ) a where cnt>1) b;

  3. (Using MySQL as language)

    SELECT COUNT(Roll No.) – COUNT(DISTINCT Roll No.) as Count of Duplicate Rows from student;

    Best answer
  4. (Using MySQL as language)

    SELECT (COUNT(Roll No.) – COUNT(DISTINCT Roll No.)) as Count of Duplicate Rows from student;

  5. SELECT COUNT(“Roll No”)-DISTINCT(“Roll No”) FROM student;

  6. If order id has duplicate values and we want to know it. Table Orders.

    SELECT OrderID, COUNT(OrderID)
    FROM Orders
    GROUP BY OrderID
    HAVING COUNT(OrderID)>1

  7. SELECT count(RollNo) FROM student GROUP BY RollNo HAVING count(*) > 1;

  8. a= count the no of rows
    b= count distinct no of rows
    print a-b

  9. select (count(roll_no) – count(distinct roll_no) ) as repeat_values
    from student

  10. SELECT Roll No COUNT(*)
    FROM student
    GROUP BY Roll No
    HAVING COUNT(*) > 1

  11. SELECT *FROM STUDENT
    GROUP BY Roll No.
    HAVING COUNT>1;

  12. Select Count(Distinct Roll No.) From student

  13. SELECT roll_num, COUNT(roll_num) FROM table
    GROUP BY roll_number
    ORDER BY COUNT(roll_num) DESC
    LIMIT 1

  14. select sum(Rep_Count)
    (
    select count(*) as Roll_Tot_count,
    count(distinct(Roll_No)) as Roll_Unq_Count ,
    (Roll_Tot_count – Roll_Unq_Count ) as Rep_Count
    from student groupby rollno
    )a
    from a

  15. SELECT COUNT(roll_no)- COUNT(DISTINCT roll_no) FROM student;

  16. Select Roll_no
    Dense_rank() Over (Partition by Roll_No Order Roll_no) As Repetitions
    from student

  17. SELECT COUNT(Roll No.) – COUNT(DISTINCT Roll No.) as Count of Duplicate Rows from student;

  18. SELECT
    ( SELECT COUNT (Roll No) FROM student) –
    ( SELECT COUNT (DISTINCT Roll No )FROM student ) as output;

  19. Select Roll No. from student having count(*) >1

    0

    #Answer
    SELECT count(Roll No.) – count(distinct(Roll No.)) from student;

    0

    SELECT count(Roll No.) – count(distinct(Roll No.)) from student;

  20. SELECT count(Roll No.)-count(distinct(Roll No.)) from student;

  21. SELECT COUNT (Roll No.) – COUNT(DISTINCT (Roll No.)) AS duplicate rows FROM student

  22. SELECT COUNT (Roll No.) – COUNT(DISTINCT (Roll No.)) AS no. of duplicate rows FROM student

  23. select count(Roll.no)-count(distinct Roll.no) as duplicate student roll.no from students;

  24. SELECT COUNT(Roll No.) – COUNT(DISTINCT(Roll No.)) AS no. of duplicate rows
    FROM student

    0

    select count(rollno) – count(distinct(rollno) as duplicate_rows from student

  25. SELECT (COUNT(roll_no) – COUNT(DISTINCT roll_no) ) as change_count
    FROM roll_num

  26. SELECT COUNT(*) – COUNT(DISTINCT Roll_No) AS duplicate_values FROM Student;

  27. select count(Roll No) – count(distinct Roll no) from student;

  28. SELECT COUNT(DISTINCT Roll No) FROM student;

    0

    SELECT COUNT (DISTINCT Roll No.) FROM student;

  29. # MySql

    select count(roll no) – count(distinct roll no) as count_of_duplicate_row from student

  30. SELECT COUNT(roll no) AS count_of_duplicate_rows
    FROM student
    GROUPBY roll no
    HAVING COUNT(roll no)>1;

  31. SELECT COUNT(roll no)
    FROM student
    GROUPBY roll no
    HAVING COUNT(roll no)>1;

  32. SELECT COUNT(roll no) AS duplicate_rows
    FROM student
    GROUPBY roll no
    HAVING COUNT(roll no)>1;

  33. Create table temp as
    select count(*)-count(distinct roll_nun) as num_dup_rows
    from table_name;
    Quit;

  34. SELECT COUNT(Id)- COUNT(DISTINCT Id) FROM ROLL;

  35. If order id has duplicate values and we want to know it. Table Orders.

    SELECT OrderID, COUNT(OrderID)
    FROM Orders
    GROUP BY OrderID
    HAVING COUNT(OrderID)>1

  36. Select count(roll
    No) – count(distinct (roll No)) as duplicates from student;

  37. select count(column_name)-distinct(column_name) from table

  38. select count(column_name)-count(distinct(column_name)) from table

  39. SELECT COUNT(Roll No.)-COUNT(DISTINCT Roll No.) FROM student;

  40. SELECT COUNT(Roll No)-COUNT(DISTINCT Roll No) FROM Student

  41. select count(Roll no.) – count(distinct(Roll no.)) as duplicated_values
    from students;

  42. SELECT COUNT(Roll No) – Count (Distinct Roll No) as duplicates From Student;

  43. select id,count(id) from student group by id;

  44. select id,count(id) from student group by id ;

  45. select RollNo,count(RollNo) from student group by id ;

  46. Total number of rows – number of distinct rows.

    SELECT COUNT(Roll no.) – COUNT(DISTINCT (Roll no.)) as duplicate rows from student

  47. select count(Roll No)-count(distinct Roll No) from student;

  48. This is a bit logical,

    > we have to create a table first (let’s say we school)
    CREATE TABLE school ( roll_no int);

    > then we have to insert the values
    INSERT INTO school VALUES(10);
    INSERT INTO school VALUES(20);
    INSERT INTO school VALUES(10);
    INSERT INTO school VALUES(10);
    INSERT INTO school VALUES(20);

    > then we have to run the query
    SELECT COUNT(roll_no) from school where roll_no=10

    This will lead to the answer

  49. Select count(roll no.) as duplicate
    from students
    order by roll no
    where count(roll no.)>1

  50. Select RollNo from student HAVING count(*)>1

  51. select count(Roll no)-count(distinct Roll no) as no_of_Duplicates from student;

  52. select count(Roll No) – count(Distinct(Roll No))

Leave an answer

Browse
Browse