subquery not giving output

Question

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?

score 3 · Accepted Answer · answered Feb 24 '13 at 02:41

3

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;

answered Feb 24 '13 at 02:41

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks! So if I understand it right, the number of attributes in the primary query and subqueries would have to match? If i had kept it the way it was? – rex Feb 24 '13 at 02:51
@rex: Yes, the number and their order. It's a match column by column. You would need parenthesis around multiple columns, though. – Erwin Brandstetter Feb 24 '13 at 02:53
A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns. for more details visit http://www.tutorialspoint.com/sql/sql-sub-queries.htm – Feb 24 '13 at 02:53
@rex: .. but only on the left side of `IN`: `SELECT (1,2) IN (SELECT 1,2)` – Erwin Brandstetter Feb 24 '13 at 03:00

score 2 · Answer 2 · answered Feb 24 '13 at 02:42

2

SELECT doctorID
FROM Hospital 
WHERE doctorID IN

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

answered Feb 24 '13 at 02:42

score 1 · Answer 3 · answered Feb 24 '13 at 02:41

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);

subquery not giving output

3 Answers3