Select * from table where count(xyz)>1

[Natalie]

I'm trying to select only the rows where the count of a certain column is greater than 1. (See subject line).

How can I do this? Do I need a nested query?

Thanks!!

Natalie

 

[rac2]

It depends on whether you want to see all of the columns in the result, and probably you do.

If not, then something like this will show you just those companies that occur in more than one row -

SELECT company
FROM applicants
GROUP BY company
HAVING COUNT(account_id) > 1

Otherwise, create a view with the foreign-key as the column to GROUP BY then JOIN the view with the base table.

CREATE VIEW multi_applicants AS
SELECT applicant_id
FROM applicants
GROUP BY applicant_id
HAVING COUNT(account_id) > 1

Then

SELECT applicants.*
FROM applicants 
JOIN multi_applicants ON applicants.applicant_id = multi_applicants.applicant_id

I believe a sub-query in this case would be just a different way of expressing the same process. There might be performance differences due to SQL Server creating different execution plans.

SELECT *
FROM applicants 
WHERE applicant_id IN
  (SELECT applicant_id
   FROM applicants
   GROUP BY applicant_id
   HAVING COUNT(account_id) > 1
  )

 

[Natalie]

Thanks alot! I forgot about HAVING!!! duh.

You're a lifesaver!

Natalie