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.

Cet exercice fait partie du cours

Introduction to Databases in Python

Afficher le cours

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!

Exercice interactif pratique

Essayez cet exercice en complétant cet exemple de 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)

Modifier et exécuter le code