CODEWITHSUNDEEP
mysqlsql
Nona
Nona

Reputation: 5462

How to handle this nested select query with arithmetic?

So I'm trying to do a nested select query with arithmetic in it to answer the following: "Show the countries in Europe with a per capita GDP greater than 'United Kingdom'."

I try something like the below with the accompanying table in SQL (MYSQL engine)

SELECT name FROM world WHERE continent='Europe' AND gdp/population > (SELECT gdp/population FROM world WHERE name='United Kingdom') 

+-----------------+-----------+---------+------------+--------------+
|      name       | continent |  area   | population |     gdp      |
+-----------------+-----------+---------+------------+--------------+
|     Japan       | Asia      |  652230 |   25500100 |  20343000000 |
|     Albania     | Europe    |   28748 |    2831741 |  12960000000 |
|     Morocco     | Africa    | 2381741 |   37100000 | 188681000000 |
|     Andorra     | Europe    |     468 |      78115 |   3712000000 |
|     Angola      | Africa    | 1246700 |   20609294 | 100990000000 |
+-----------------+-----------+---------+------------+--------------+

I get a syntax error - what is the correct way to write this and what am I doing wrong? I'm trying to improve at SQL.

Upvotes: 0

Views: 186

Answers (3)

Ramesh Dahiya
Ramesh Dahiya

Reputation: 183

You can use joins instead of doing sub query as RDBMS can create execution plan that is better for your query and can predict what data should be loaded to be processed and save time, unlike the sub-query where it will run all the queries and load all their data to do the processing. You query would look like this.

SELECT w1.* from world w1 join world w2 
where w2.name ='uk'
and w1.gdp/w1.pop > w2.gdp/w2.pop
and w1.continent='Europe' and w2.continent='Europe'

Upvotes: 0

Pantamtuy
Pantamtuy

Reputation: 243

you forgot the quote

change

name='United Kingdom

to

name='United Kingdom'

Upvotes: 2

pavel
pavel

Reputation: 27082

Missing quote at the end of SQL query:

SELECT name 
FROM world 
WHERE continent='Europe' AND 
      gdp/population > (SELECT gdp/population FROM world WHERE name='United Kingdom')
                                                                                   ^

Upvotes: 1

Related Questions