ابدأ بالتواصل مع الأشخاص وتبادل معارفك المهنية

أنشئ حسابًا أو سجّل الدخول للانضمام إلى مجتمعك المهني.

متابعة

Do you know how to count duplicate values in sql ?

user-image
تم إضافة السؤال من قبل mohamed khiry , Senior Database Administrator , confidential
تاريخ النشر: 2016/09/27
Souleimane BELARIBI
من قبل Souleimane BELARIBI , BigData / Business Intelligence Consultant Intern , Necker Enfants Malades Hospital

SELECT name, email, COUNT(*)FROM users GROUPBY name, email HAVING COUNT(*)>1

Morteza Nemati
من قبل Morteza Nemati , Coe & Founder , Ravin Data Process

for count douplicate value in sql can use Group By and Having...

SELECT FIELD, COUNT(FIELD) FROM TABLE GROUP BY FIELD HAVING COUNT(FIELD)>1;

Rezeq Tawfiq Abdel Hafez Abu Rezeq
من قبل Rezeq Tawfiq Abdel Hafez Abu Rezeq , System Administartor , IBM - Contractor

select column_name,count(column_name) from Table_namehaving count(column_name) > 1group by column_name

khalid Hassanien
من قبل khalid Hassanien , Financial Manger , Alrwania Ltd

If you have a column with duplicated values, and you want to know what are those duplicated values are and how many duplicates are there for each of those values, you can use the "GROUP BY ... HAVING" clause.

To count all the duplicate records in a column of the table use this code:

SELECT Column_name, COUNT(*) Count_Duplicate FROM Table_name GROUP BY Column_name HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

To count all the duplicate records in two columns of the table:

SELECT Column1, Column2, COUNT(*) Count_Duplicate FROM Table_name GROUP BY Column1, Column2 HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

To count all the duplicate records in all columns of the table:

SELECT , COUNT(*) Count_Duplicate FROM Table_name GROUP BY HAVING COUNT(*) > 1 ORDER BY COUNT(*) DESC

Falak Shaer
من قبل Falak Shaer , Project Coordinator , Bader H Al-Hussini Sons & Co.

SELECT name, email, COUNT(value) 

FROM users 

GROUP BY name, email

HAVING COUNT(value) > 1

Riadh ZRIBI
من قبل Riadh ZRIBI , Responsable du système d'information , SIMOP-Tunisie

Using group by  . exemple :

 

Select product_code, product_name , count(*) from product 

group by product_code, product_name  

having count(*) > 1

 

Here we want to know which products found in the result more than one time .

Jay Nagar
من قبل Jay Nagar , Database Administrator , Universal Software

SELECT COLUMN_NAME,COUNT(COLUMN_NAME) GROUP BY COLUMN_NAME HAVING COUNT(*)>1

Enas  Fares
من قبل Enas Fares , Syria , Syriatel

select *

form tables,

Group by coulmnName,

having count(*)>1;

Sajid Z Saiyed
من قبل Sajid Z Saiyed , Senior Software Engineer - Team Leader , Emirates Airline

below query to find duplicate for given columns

SELECT name, email, COUNT(*)FROM users GROUPBY name,email HAVING COUNT(*)>1 

Mathew Joseph
من قبل Mathew Joseph , Architect , WIPRO

Use count(*)  group by on the column you want to find duplicates and then filter count(*) >1

المزيد من الأسئلة المماثلة

هل تحتاج لمساعدة في كتابة سيرة ذاتية تحتوي على الكلمات الدلالية التي يبحث عنها أصحاب العمل؟