While working on a query to count some rows based upon a condition I found an interesting thing.If you were to use the COUNT function in a query for a condition then we have to use the HAVING clause instead of WHEREclause.
So the following query would return error:
$wrong_query = “SELECT count( `fk_mp` ) FROM wrong_tab GROUP BY`pg_name` WHERE count( `fk_mp` ) > 1”;
Now, compose the same query using HAVING instead of WHERE :
$right_query = “SELECT count( `fk_mp` ) FROM wrong_tab GROUP BY`pg_name` HAVING count( `fk_mp` ) > 1”;
This query can also be used to find duplicate records in a table.