Calculating a difference between two columns

Often, you'll need to perform math operations as part of a query, such as if you wanted to calculate the change in population from 2000 to 2008. For math operations on numbers, the operators in SQLAlchemy work the same way as they do in Python.

You can use these operators to perform addition (+), subtraction (-), multiplication (*), division (/), and modulus (%) operations. Note: They behave differently when used with non-numeric column types.

Let's now find the top 5 states by population growth between 2000 and 2008.

This exercise is part of the course

Introduction to Databases in Python

View Course

Exercise instructions

Define a select statement called stmt to return:
- i) The state column of the census table (census.columns.state).
- ii) The difference in population count between 2008 (census.columns.pop2008) and 2000 (census.columns.pop2000) labeled as 'pop_change'.
Group the statement by census.columns.state.
Order the statement by population change ('pop_change') in descending order. Do so by passing it desc('pop_change').
Use the .limit() method on the previous statement to return only 5 records.
Execute the statement and fetchall() the records.
The print statement has already been written for you. Submit the answer to view the results!

Hands-on interactive exercise

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

# Build query to return state names by population difference from 2008 to 2000: stmt
stmt = select([____, (____-____).label(____)])

# Append group by for the state: stmt_grouped
stmt_grouped = stmt.group_by(____)

# Append order by for pop_change descendingly: stmt_ordered
stmt_ordered = stmt_grouped.order_by(____)

# Return only 5 results: stmt_top5
stmt_top5 = ____

# Use connection to execute stmt_top5 and fetch all results
results = connection.execute(____).fetchall()

# Print the state and population change for each record
for result in results:
    print('{}:{}'.format(result.state, result.pop_change))

Edit and Run Code