Count of records by state

Often, we want to get a count for each record with a particular value in another column. The .group_by() method helps answer this type of query. You can pass a column to the .group_by() method and use in an aggregate function like sum() or count(). Much like the .order_by() method, .group_by() can take multiple columns as arguments.

This exercise is part of the course

Introduction to Databases in Python

View Course

Exercise instructions

Import func from sqlalchemy.
Build a select statement to get the value of the state field and a count of the values in the age field, and store it as stmt.
Use the .group_by() method to group the statement by the state column.
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 a query to select the state and count of ages by state: 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