Sujatha

• Aug 1st, 2006

 

Hi All,

Instead of this we can use GROUP BY Clause with HAVING condition.

For ex,

Select count(*),lastname from tblUsers group by lastname having count(*)>1

This query return the duplicated lastnames values in the lastname column from tblUsers table.

gunjan28

 Profile Answers by gunjan28 

• Nov 11th, 2009

 

When using a count(distinct) it is better to use self-join
Exp- in employees table to get manager name and total employees including manager querry will be-
id first_name last_name manager
----------- --------------- --------------- -----------
1 Pat Crystal NULL
2 Dennis Miller 1
3 Jacob Smith 1
4 Allen Hunter 2
5 Mary Underwood 3
6 Joy Needham 3

SELECT m.First_Name AS 'ManagerFN', m.Last_Name AS 'ManagerLN',
       COUNT(DISTINCT e.id) As 'TotalEmpIncludMang'
FROM  employees AS e LEFT OUTER JOIN employees AS m
ON  m.manager=e.id