1. Learn
    2. /
  2. Courses
    3. /
  3. Introduction to Databases in Python

Exercise

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.

Instructions

100 XP
  • 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 hit 'Submit Answer' and check out the results!