RE: How we can count duplicate entery in particular ta...
Every table should have a primary key defined. If that is the case the RDBMS will not allow a duplicate row to be inserted. The INSERT statement will fail and the table will never have a duplicate (neither will the unique index).
RE: How we can count duplicate entery in particular ta...
The syntax in the previous answer (where count(*) > 1) is very questionable. suppose you think that you have duplicate employee numbers. there's no need to count them to find out which values were duplicate but the followin SQL will show only the empnos that are duplicate and how many exist in the table:
Select empno, count(*)
from employee
group by empno
having count(*) > 1
Generally speaking aggregate functions (count, sum, avg etc.) go in the HAVING clause. I know some systems allow them in the WHERE clause but you must be very careful in interpreting the result. WHERE COUNT(*) > 1 will absolutely NOT work in DB2 or ORACLE. Sybase and SQLServer is a different animal.
RE: How we can count duplicate entery in particular ta...
select count(*) from tablename where count(*) >1 group by empno;this query generate an error use right syntex groupbyI have a table name -> empfields -> id (int autoincremented),name (varchar),sal(int)used query ->select count(*) from emp where count(*) >1 group by id;