CODEWITHSUNDEEP
sqldatabasesql-server-2008
Sam
Sam

Reputation: 325

SQL - Display when count > 1

I have the following query that I am sure is incorrect. I have a table called KantechImages and it contains about 8500 rows. I want to display Name, ESRno, Dept & JobTitle where there is more than 1 occurence of someones Name. My query is:

SELECT     Name, COUNT(*) AS count, ESRno, JobTitle, Dept
FROM       dbo.KantechImages
GROUP BY   Name, ESRno, JobTitle, Dept
HAVING     (COUNT(*) > 1)

But it is only displaying 268 rows, which I know is incorrect. If I edit it to just SELECT Name & Count, it brings back over 500 rows.

Upvotes: 0

Views: 71

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You query looks ok. I think the problem is with your data. Probably something you think are the same and they arent like extra spaces in the name. Or maybe bad matchs between names and ESRno.

Try something like

This should return the same +500 name, but order by Name you can compare if some have extra spaces and appear duplicated.

  SELECT Name, count(Name)
  FROM   dbo.KantechImages
  GROUP BY Name
  ORDER BY Name
  HAVING count(Name) > 1

This should return the same +500 because I assume each Name have a single ESRno, unless two ppl have same name. In that case you should get even more rows in your result.

  SELECT Name, ESRno, Count(ESRno)
  FROM   dbo.KantechImages
  GROUP BY Name, ESRno
  ORDER BY Name, ESRno
  HAVING count(ESRno) > 1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269823

You can do what you want with window functions:

select name, cnt, ESRno, JobTitle, Dept
from (select ki.*, count(*) over (partition by name) as cnt
      from dbo.KantechImages ki
     ) ki
where cnt > 1;

Because you want the original rows, a group by in the outer select is not appropriate.

Upvotes: 2

Related Questions