SQlite query errror: OperationalError: near "(": syntax error when using multiple tables

Viewed 116

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

1 Answers

The SUM is in the wrong place. It should be

SELECT SUM(countries.population)
Related