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
Exercise instructions
- Save an alias of the
employees
table asmanagers
. - Build a query to select the
name
column of themanagers
table and the count of the number of their employees. The functionfunc.count()
has been imported and will be useful! Use it to count theid
column of theemployees
table. - Using a
.where()
clause, filter the records where theid
column of themanagers
table andmgr
column of theemployees
table are equal. - Group the query by the
name
column of themanagers
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)