How to find average of numbers in database column?-CodePudding

I am trying to find the average of all the populations in the cities table. I am working on a project, and these are the instructions. I already completed the first step: When I try to get the "city_population"s from the "cities" table and divide the sum by the items in the column, I get an error. This is my code:

import sqlite3
import os 

# Remove Database file if it exists:

os.remove('cities.db')

connection = sqlite3.connect("cities.db")
cursor = connection.cursor()

cursor.execute("create table if not exists cities(city_id INTEGER PRIMARY KEY NOT NULL, cities_name text, city_population integer)")
    
cities_list = [('Minneapolis', '425,336'),
    ('St. Paul', '307,193'),
    ('Dallas', '1,288,000'),
    ('Memphis', '628, 127'),
    ('San Francisco', '815,201'),
    ('Milwaukee', '569,330'),
    ('Denver', '711,463'),
    ('Phoenix', '1,625,000'),
    ('Chicago', '2,697,000'),
    ('New York', '8,468,000')]

cursor.executemany("INSERT OR IGNORE INTO cities VALUES (NULL, ?, ?)", cities_list)

# Print cities in alphabetical order

cursor.execute("select cities_name from cities")
result = sorted(cursor.fetchall())
print(result)

# Print average (NEED HELP HERE!)

cursor.execute("select city_population from cities")
result = cursor.fetchall()

average = sum(result) / len(result)
print(average)


connection.commit()

connection.close()

I was hoping for the average of the populations to be displayed, but I just get an error. How can I fix it?

CodePudding user response：

Your source population numbers contain commas, you will need to remove those.

The database is returning a tuple which can not use the sum() function on it's own. You can address this a number of ways, this worked for me :

average =  sum(list(map(sum, list(result)))) / len(result)

Source: https://www.geeksforgeeks.org/python-sum-of-tuple-elements/

In the future, please provide the error message.