CODEWITHSUNDEEP
mysqlsql
Misakumi
Misakumi

Reputation: 1

Subquery returns more than 1 row, how can I solve?

Question: Show the category of competitions that have always been hosted in the same country during May 2010. What is wrong with my query?

select Category
from competition
where Date >= '2010-01-01' and Date <= '2010-12-31'
group by Country, Category
having count(*) = (select count(*)
            from competition
            where Date >= '2010-01-01' and Date <= '2010-12-31'
            group by Category)

Upvotes: 0

Views: 51

Answers (3)

Barmar
Barmar

Reputation: 780974

You don't need two queries. Just use one query that checks that the count of countries is 1.

select category, count(DISTINCT country) AS country_count
from competition
where Date BETWEEN '2010-05-01' and '2010-05-31'
group by Category
HAVING country_count = 1

I also corrected the dates to be just May, not the whole year 2010.

Upvotes: 2

James McPherson
James McPherson

Reputation: 2556

I'd try something like this to start with:

SELECT COUNTRY
, CATEGORY
, COUNT(COUNTRY)
FROM COMPETITION
WHERE DATE BETWEEN '2010-04-30' AND '2010-06-01'
ORDER BY CATEGORY DESC, COUNT(COUNTRY) DESC
;

Your original query's date limits are just for the year of 2010 but you specified you only wanted May 2010. If the Date column is a date or datetime time you'll need to cast the string to the appropriate datatype.

Your question asked "always hosted by one country" - do you know that a competition is only going to be hosted by one country during that particular month? If you do, you're pretty much done. If you don't, however, then you need to clarify what your criteria really are

Upvotes: 0

gh9
gh9

Reputation: 10703

Remove the GROUP BY if you that is making you return more than 1 row (in your HAVING CLAUSE. If you give me an example dataset and what you want I can help you more

Upvotes: 0

Related Questions