Leveraging functions and group_bys with hierarchical data

It's also common to want to roll up data which is in a hierarchical table. Rolling up data requires making sure you're careful which alias you use to perform the group_bys and which table you use for the function.

Here, your job is to get a count of employees for each manager.

This exercise is part of the course

Introduction to Databases in Python

View Course

Exercise instructions

Save an alias of the employees table as managers.
Build a query to select the name column of the managers table and the count of the number of their employees. The function func.count() has been imported and will be useful! Use it to count the id column of the employees table.
Using a .where() clause, filter the records where the id column of the managers table and mgr column of the employees table are equal.
Group the query by the name column of the managers table.
Execute the statement and store all the results. Print the names of the managers and their employees. This code has already been written so submit the answer and check out the results!

Hands-on interactive exercise

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

# Make an alias of the employees table: managers
managers = ____

# Build a query to select names of managers and counts of their employees: stmt
stmt = select([____, func.count(____)])

# Append a where clause that ensures the manager id and employee mgr are equal
stmt_matched = stmt.____

# Group by Managers Name
stmt_grouped = stmt_matched.group_by(____)

# Execute statement: results
results = connection.execute(stmt_grouped).fetchall()

# print manager
for record in results:
    print(record)

Edit and Run Code