CODEWITHSUNDEEP
sqlsql-server
Hello-World
Hello-World

Reputation: 9555

why does this select not work

I'm trying to learn sql and I get this error - what does it mean?.

I'm trying to : Get countries that have a GDP greater than any country in Europe?

Error:

[Microsoft][ODBC SQL Server Driver][SQL Server]Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. (SQL-21000)

select name, gdp from bbc 
where gdp > 
(
    select gdp from bbc
    where region = 'Europe'
)

Upvotes: 1

Views: 858

Answers (7)

Dmytro Shevchenko
Dmytro Shevchenko

Reputation: 34591

The reason of the error is that your subquery returns multiple values.

Depending on your requirements, you should use either this query, if you need gdp to be greater than EACH of the Europe's GDP's:

SELECT
  name,
  gdp
FROM
  bbc 
WHERE gdp > 
  (
    SELECT
      MAX(gdp)
    FROM
      bbc
    WHERE
      region = 'Europe'
  )

Or this one, if you need gdp to be greater than AT LEAST ONE of the Europe's GDP's (the difference is just MIN vs MAX):

SELECT
  name,
  gdp
FROM
  bbc 
WHERE gdp > 
  (
    SELECT
      MIN(gdp)
    FROM
      bbc
    WHERE
      region = 'Europe'
  )

Upvotes: 1

dani herrera
dani herrera

Reputation: 51665

Nice place to dust off academic ANY operator!

select name, gdp from bbc 
where gdp > ANY
(
select gdp from bbc
where region = 'Europe'
)

"... greater than ANY country in Europe ...". Just like answer.

Upvotes: 1

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726579

The message describes the error adequately: your table has many countries in Europe, so > operator cannot be applied without some aggregation or more constraints in the subquery:

select name, gdp from bbc 

where gdp > 

(
select max(gdp) from bbc
where region = 'Europe'

)

Adding max ensures that there would be precisely one number selected, and that number is the highest GDP among all European countries1.

1 You might as well select the GDP of Germany right away :)

Upvotes: 1

Aghilas Yakoub
Aghilas Yakoub

Reputation: 28970

You can try with one value - based on Min , Max or TOP 1

Nota : You can't compare one value with collection.

select name, gdp from bbc 

where gdp > 

(select TTOP(1) gdp from bbc where region = 'Europe')

Or 

 select name, gdp from bbc 

    where gdp > 

    (select Max(gdp) from bbc where region = 'Europe')

Upvotes: 1

Michael Ballent
Michael Ballent

Reputation: 1088

run this part of your query

select gdp from bbc where region = 'Europe'

if it is returning more than 1 row it will fail. You need to be more selective in the where clause.

Upvotes: 1

Trevor
Trevor

Reputation: 6689

This means that the query select gdp from bbc where region = 'Europe' returns more than 1 row and the where gpd > condition can't compare against multiple values. You will need to update the query to only return 1 row.

Upvotes: 3

TechDo
TechDo

Reputation: 18629

It occurs when the inner query returns more than 1 value. Try:

select name, gdp from bbc where gdp > (select TOP 1 gdp from bbc where region = 'Europe')

Upvotes: 0

Related Questions