One Stop GATE Forum: MYSQL INTERVIEW QUESTIONS

Message

How we can count duplicate entery in particular table against Primary Key ? What are constraints?

Answer
:

    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.

Author	Message
Priya Groupie Joined: 04Jan2007 Online Status: Offline Posts: 82	Topic: MYSQL INTERVIEW QUESTIONS Posted: 15Feb2007 at 9:59am
	How we can count duplicate entery in particular table against Primary Key ? What are constraints? Answer : 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.
	For More Sample Papers Visit: http://onestopgate.com/gate-preparation/ Post Resume: Click here to Upload your Resume & Apply for Jobs

	IP Logged


One Stop GATE Forum : GATE Technical Discussions : GATE CS