I am trying to show the total population per continent using two different tables with SQlite's query function.
query_join = '''
SELECT countries.SUM(population) AS "total population", continents.name AS "continent name"
FROM countries, continents
WHERE countries.continent = continents.code
GROUP BY continent;
'''
result_join = pd.read_sql_query(query_join, conn_countries)
result_join
When I try to run it, I get the following error message:
OperationalError Traceback (most recent call last)
C:\ProgramData\Anaconda3\lib\site-packages\pandas\io\sql.py in execute(self=<pandas.io.sql.SQLiteDatabase object>, *args=('\n SELECT countries.SUM(population) AS "total ...tinent = continents.code\n GROUP BY continent;\n',), **kwargs={})
1430 else:
-> 1431 cur.execute(*args)
cur.execute = <built-in method execute of sqlite3.Cursor object at 0x000002711B4100A0>
args = ('\n SELECT countries.SUM(population) AS "total population", continents.name AS "continent name"\n FROM countries, continents\n WHERE countries.continent = continents.code\n GROUP BY continent;\n',)
1432 return cur
OperationalError: near "(": syntax error
During handling of the above exception, another exception occurred:
DatabaseError Traceback (most recent call last)
<ipython-input-127-2ee18e8072cb> in <module>
7 GROUP BY continent;
8 '''
----> 9 result_join = pd.read_sql_query(query_join, conn_countries)
global result_join = total population continent name
0 13068161 Africa
1 0 Antarctica
2 4975593 Asia
3 84000 Europe
4 86754 North America
5 57881 Oceania
6 41343201 South America
global pd.read_sql_query = <function read_sql_query at 0x000002711A376268>
global query_join = '\n SELECT countries.SUM(population) AS "total population", continents.name AS "continent name"\n FROM countries, continents\n WHERE countries.continent = continents.code\n GROUP BY continent;\n'
global conn_countries = <sqlite3.Connection object at 0x000002711A1911F0>
10
I believe the mistake is somewhere in the SUM() part, since I don't get the error when I leave that part out. The problem with that is that if I do that, it shows
0 13068161 Africa
1 0 Antarctica
2 4975593 Asia
3 84000 Europe
4 86754 North America
5 57881 Oceania
6 41343201 South America
Which is quite obviously incorrect.
Can someone figure out what I'm doing wrong? I know a similar question has already been asked before, but I couldn't figure out how to apply the answers that were given there to my own problem.
Thank you