Guided Project: CIA Factbook SQL query problem

https://stackoverflow.com/questions/38621766/using-a-sub-query-to-list-greater-than-averages

I am trying to edit the above to find countries with land area above average but it does not work please advise what is wrong:

Which countries have an area_land greater than the world average?

%%sql
SELECT name, AVG(area_land) AS “KOMPARE”
FROM facts,
(SELECT name, AVG(area_land)
FROM facts
GROUP BY area_land) subquery1
WHERE facts.area_land = subquery1.area_land
GROUP BY area_land
HAVING area_land > AVG(area_land)

Hi @jamesberentsen,

the name is ambiguous, because you have two sets (facts and subquery1, which both have the name column.

If I understand correctly you want the result as follows:

name, area
country 1, area 1,
…, …

You have to modify the SELECT statement. Right now this will return the name and average for column area_land.
I think the WHERE statement will also filter the results and leave those, whose area_land equals that of average(area_land) which may result is an empty set.

1 Like

Thanks for your message kakoori,
However I still get same error message of ambiguous column, when I tried to fix.
I just read it is advisable to table qualify column references in subqueries, so I suppose it is possible if I understand what you meant.

Below link talks about column names lacking attribute reference however I have that here, so I don’t know what problem is causing error.
WHERE facts.area_land = subquery1.area_land
error message info
Yes that is the result I want name,area, country1,area1

It’s the first name in SELECT that is ambiguous.

Notice that you ask SQL to fetch name from (facts, subquery1). He goes nuts and gives an error asking which name do you mean, facts.name or subquery1.name?

1 Like

Hi there,

I amended thanks, but now I get a different error