Find the number of employees working under a manager

I have three tables called branch,employee and manager structured like this

Table: branch
CREATE TABLE branch (
    id            INTEGER PRIMARY KEY AUTOINCREMENT
                          NOT NULL
                          UNIQUE,
    name          FLOAT,
    city          VARCHAR,
    region        VARCHAR,
    postal_code   VARCHAR,
    street        VARCHAR,
    street_number VARCHAR
);

Table: employee
CREATE TABLE employee (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    surname       VARCHAR,
    name          VARCHAR,
    branch_id     INTEGER REFERENCES BRANCH (ID),
    city          VARCHAR,
    region        VARCHAR,
    street        VARCHAR,
    street_number VARCHAR,
    phone         VARCHAR,
    mobile        VARCHAR,
    vat           VARCHAR,
    amka          VARCHAR,
    at            VARCHAR
);


Table: manager
CREATE TABLE manager (
    id          INTEGER PRIMARY KEY AUTOINCREMENT,
    employee_id INTEGER REFERENCES employee (id) 
                        UNIQUE
                        NOT NULL
);

I want to view the id of each manager and the count of the employees he has under his supervision like this

Manager   Number of employees


   1         5
   2        13

I have made this so far that shows which branch each manager works on but i don’t know how to proceed with the counting.

SELECT manager.id AS [Manager id],
       employee.name AS [Manager name],
       branch.name AS [Branch name],
       branch.id AS [Branch id]
  FROM (
           (
               manager
               INNER JOIN
               employee ON manager.employee_id = employee.id
           )
           INNER JOIN
           branch ON employee.branch_id = branch.id
       );

The solution i have in mind is first find in which branch each manager is working and then count all the employees of that branch minus one ( the manager). The problem is i don’t know how to exactly express it in sql.

Answer

Since each branch can only have one manager, you can count the number of employees in a branch and then join the manager to their branch. From there you can count the number of employees in each branch.

SELECT manager.id               AS manager_id,
       manager_employee.name    AS manager_name,
       COUNT(employee.id) - 1   AS employee_count
  FROM manager
 INNER JOIN employee AS manager_employee
    ON manager.employee_id = manager_employee.id
 INNER JOIN branch
    ON manager_employee.branch_id = branch.id
 INNER JOIN employee
    ON branch.id = employee.branch_id
 GROUP BY manager.id,
          manager_employee.name