CODEWITHSUNDEEP
sqlsql-serversql-server-ce
h bob
h bob

Reputation: 3780

Select stuff where exists in subquery

I want to count the number of records with duplicated values, and then get all sorts of other data related to it. Like this:

SELECT Id, Name, PhoneNumber, Code, CodeCount from Person
WHERE Code IN (SELECT Code, COUNT(Code) AS CodeCount
               FROM Person
               GROUP BY Code
               HAVING COUNT(Code) > 1);

But I get the error The columns in the subquery do not match.

How do I make this work?

Upvotes: 0

Views: 564

Answers (4)

Nguyễn Hải Triều
Nguyễn Hải Triều

Reputation: 1464

SQL 2015+: SELECT P.Id, P.Name, P.PhoneNumber, P.Code, Q.CodeCount from Person P CROSS APPLY (SELECT Id, COUNT(Code) OVER(PARTITION BY Code) AS CodeCount FROM Person) AS Q WHERE P.Id = Q.Id AND Q.CodeCount > 1

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270703

Note: this doesn't work on SQL Server CE, but it does work on SQL Server 2005+.

Another way to approach this is using window functions:

select p.*
from (select p.*, count(*) over (partition by code) as cnt
      from person p
     ) p
where cnt > 1;

Because the in requires both an aggregation and a join (implicitly because of the in), this version will often have better performance. It can also make use of an index on person(code).

Upvotes: 2

japzdivino
japzdivino

Reputation: 1746

This will work on you

    SELECT Id, Name, PhoneNumber, Code, COUNT(Code) AS 'CodeCount' 
    from Person WHERE Code IN (SELECT Code FROM Person) 
    GROUP BY Id, Name, PhoneNumber, Code HAVING COUNT(Code) > 1;

Upvotes: 1

eugenioy
eugenioy

Reputation: 12393

You are doing "Code IN (...)", but the subquery is returning two columns instead of one.

Remove the additional column from the subquery.

You don't need to return the count. The HAVING clause will work just the same even if you don't return that column in the subquery.

SELECT Id, Name, PhoneNumber, Code, CodeCount from Person
WHERE Code IN (SELECT Code
           FROM Person
           GROUP BY Code
           HAVING COUNT(Code) > 1);

Upvotes: 1

Related Questions