CODEWITHSUNDEEP
mysqlsqlsubqueryaggregate-functions
rex
rex

Reputation: 339

subquery not giving output

I am trying to run a query that returns a list of IDs that appear more than 3 times in a table called Hospital. So far I have come up with this:

SELECT doctorID
FROM Hospital 
WHERE doctorID IN

(SELECT temp.doctorID, count(temp.doctorID) as frequency
FROM Hospital as temp
GROUP BY temp.doctorID
HAVING frequency >3);

but for some reason i get an error when i run it

Code: 1241. Operand should contain 1 column(s)

Seemed to be a popular error when I looked around, but I can't find an existing solution that applies to me. When I run the query that in parenthesis by itself, it works fine. So how come it doesn't work as a subquery?

Upvotes: 2

Views: 131

Answers (3)

user2063626
user2063626

Reputation: 

SELECT doctorID
FROM Hospital 
WHERE doctorID IN

(SELECT temp.doctorID 
FROM Hospital as temp
GROUP BY temp.doctorID
HAVING count(temp.doctorID)>3);

Upvotes: 2

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659227

Remove the second column from the subquery, move the count() into the HAVING clause:

SELECT doctorID
FROM Hospital 
WHERE doctorID IN

(SELECT temp.doctorID
FROM Hospital AS temp
GROUP BY temp.doctorID
HAVING count(temp.doctorID) > 3);

You could have the same result simpler, without subquery:

SELECT doctorID
FROM   Hospital
GROUP  BY doctorID
HAVING count(doctorID) > 3;

Upvotes: 3

sgeddes
sgeddes

Reputation: 62861

Your subquery is returning 2 columns. Remove the COUNT column from your SELECT list like this:

SELECT doctorID
FROM Hospital 
WHERE doctorID IN
   (SELECT temp.doctorID
   FROM Hospital as temp
   GROUP BY temp.doctorID
   HAVING count(temp.doctorID) >3);

Upvotes: 1

Related Questions