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
SQL
3 years
62 Answers
2171 views
Grand Master 4
Answers ( 62 )
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
(Using MySQL as a language)
Select COUNT(Roll No.) – COUNT(distinct Roll No.) as Count of duplicate Rows from student;
select Roll no , count(Roll no) as Roll_no from student
group by Roll no;
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;
(Using MySQL as language)
SELECT COUNT(Roll No.) – COUNT(DISTINCT Roll No.) as Count of Duplicate Rows from student;
op
Select sum(count(rollno.) From student group by rollno
Having count(rollno) > 1
Hey Ramneek, if you use that query than the output is not 3 rather it will show 10-3 and 20-2 time. But the question asked is count all the duplicate rows.
(Using MySQL as language)
SELECT (COUNT(Roll No.) – COUNT(DISTINCT Roll No.)) as Count of Duplicate Rows from student;
SELECT COUNT(“Roll No”)-DISTINCT(“Roll No”) FROM student;
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
SELECT count(RollNo) FROM student GROUP BY RollNo HAVING count(*) > 1;
a= count the no of rows
b= count distinct no of rows
print a-b
SELECT COUNT(Id)- COUNT(DISTINCT Id) FROM ROLL;
select (count(roll_no) – count(distinct roll_no) ) as repeat_values
from student
SELECT Roll No COUNT(*)
FROM student
GROUP BY Roll No
HAVING COUNT(*) > 1
SELECT *FROM STUDENT
GROUP BY Roll No.
HAVING COUNT>1;
Select Count(Distinct Roll No.) From student
SELECT roll_num, COUNT(roll_num) FROM table
GROUP BY roll_number
ORDER BY COUNT(roll_num) DESC
LIMIT 1
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
SELECT COUNT(roll_no)- COUNT(DISTINCT roll_no) FROM student;
Select Roll_no
Dense_rank() Over (Partition by Roll_No Order Roll_no) As Repetitions
from student
SELECT COUNT(Roll No.) – COUNT(DISTINCT Roll No.) as Count of Duplicate Rows from student;
SELECT
( SELECT COUNT (Roll No) FROM student) –
( SELECT COUNT (DISTINCT Roll No )FROM student ) as output;
Select Roll No. from student having count(*) >1
#Answer
SELECT count(Roll No.) – count(distinct(Roll No.)) from student;
SELECT count(Roll No.) – count(distinct(Roll No.)) from student;
SELECT count(Roll No.)-count(distinct(Roll No.)) from student;
SELECT COUNT (Roll No.) – COUNT(DISTINCT (Roll No.)) AS duplicate rows FROM student
SELECT COUNT (Roll No.) – COUNT(DISTINCT (Roll No.)) AS no. of duplicate rows FROM student
select count(Roll.no)-count(distinct Roll.no) as duplicate student roll.no from students;
SELECT COUNT(Roll No.) – COUNT(DISTINCT(Roll No.)) AS no. of duplicate rows
FROM student
select count(rollno) – count(distinct(rollno) as duplicate_rows from student
SELECT (COUNT(roll_no) – COUNT(DISTINCT roll_no) ) as change_count
FROM roll_num
SELECT COUNT(*) – COUNT(DISTINCT Roll_No) AS duplicate_values FROM Student;
select count(Roll No) – count(distinct Roll no) from student;
SELECT COUNT(DISTINCT Roll No) FROM student;
SELECT COUNT (DISTINCT Roll No.) FROM student;
# MySql
select count(roll no) – count(distinct roll no) as count_of_duplicate_row from student
SELECT COUNT(roll no) AS count_of_duplicate_rows
FROM student
GROUPBY roll no
HAVING COUNT(roll no)>1;
SELECT COUNT(roll no)
FROM student
GROUPBY roll no
HAVING COUNT(roll no)>1;
SELECT COUNT(roll no) AS duplicate_rows
FROM student
GROUPBY roll no
HAVING COUNT(roll no)>1;
Create table temp as
select count(*)-count(distinct roll_nun) as num_dup_rows
from table_name;
Quit;
SELECT COUNT(Id)- COUNT(DISTINCT Id) FROM ROLL;
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
Select count(roll
No) – count(distinct (roll No)) as duplicates from student;
select count(column_name)-distinct(column_name) from table
select count(column_name)-count(distinct(column_name)) from table
SELECT COUNT(Roll No.)-COUNT(DISTINCT Roll No.) FROM student;
SELECT COUNT(Roll No)-COUNT(DISTINCT Roll No) FROM Student
select count(Roll no.) – count(distinct(Roll no.)) as duplicated_values
from students;
SELECT COUNT(Roll No) – Count (Distinct Roll No) as duplicates From Student;
select id,count(id) from student group by id;
select id,count(id) from student group by id ;
select RollNo,count(RollNo) from student group by id ;
Total number of rows – number of distinct rows.
SELECT COUNT(Roll no.) – COUNT(DISTINCT (Roll no.)) as duplicate rows from student
select count(Roll No)-count(distinct Roll No) from student;
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
Select count(roll no.) as duplicate
from students
order by roll no
where count(roll no.)>1
Select RollNo from student HAVING count(*)>1
select count(Roll no)-count(distinct Roll no) as no_of_Duplicates from student;
select count(Roll No) – count(Distinct(Roll No))