Determining the population sum by state

To avoid confusion with query result column names like count_1, we can use the .label() method to provide a name for the resulting column. This gets appended to the function method we are using, and its argument is the name we want to use.

We can pair func.sum() with .group_by() to get a sum of the population by State and use the label() method to name the output.

We can also create the func.sum() expression before using it in the select statement. We do it the same way we would inside the select statement and store it in a variable. Then we use that variable in the select statement where the func.sum() would normally be.

This exercise is part of the course

Introduction to Databases in Python

View Course

Exercise instructions

Import func from sqlalchemy.
Build an expression to calculate the sum of the values in the pop2008 field labeled as 'population'.
Build a select statement to get the value of the state field and the sum of the values in pop2008.
Group the statement by state using a .group_by() method.
Execute stmt using the connection to get the count and store the results as results.
Print the keys/column names of the results returned using results[0].keys().

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

# Import func
____

# Build an expression to calculate the sum of pop2008 labeled as population
pop2008_sum = func.sum(____).label(____)

# Build a query to select the state and sum of pop2008: stmt
stmt = select([____, ____])

# Group stmt by state
stmt = stmt.group_by(____)

# Execute the statement and store all the records: results
results = connection.execute(____).fetchall()

# Print results
print(results)

# Print the keys/column names of the results returned
print(____)

Edit and Run Code