This exercise gived 5 table:

1. Company (company_code, founder)
2. Lead_Manager (lead_manager_code, company_code)
3. Senior_Manager (senior_manager_code,
   lead_manager_code, company_code)
4. Manager (manager_code,
   senior_manager_code, lead_manager_code, company_code)
5. Employee
   (employee_code, manager_code, senior_manager_code,
   lead_manager_code, company_code)

Note: read above as : Table(column, column..)

must return:
print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.
Example: C1 Monika 1 2 1 2
Explaination example: In company C1, the only lead manager is LM1. There are two senior managers, SM1 and SM2, under LM1. There is one manager, M1, under senior manager SM1. There are two employees, E1 and E2, under manager M1.

I tried myself a bit but it doesn’t work, below is what I did. I didn’t use join too at first until I decided to look at discussion. I didn’t make short form? like how example answer use e,a. Is that that reason or is there any particular basics that I didn’t grasp here?

select Company.company_code, Company.founder,
count(distinct(Lead_Manager.lead_manager_code)),count(distinct(Senior_Manager.senior_manager_code)),
count(distinct(Manager.manager_code)),count(distinct(Employee.employee_code)) FROM Company

join employee.company_code =company.company_code
group by company.company_code,company.founder
order by company.company_code asc;

Here is example answer that worked:

select  a.company_code , a.founder , 
count(distinct(e.lead_manager_code)),count(distinct(e.senior_manager_code)),
count(distinct(e.manager_code)),count(distinct(e.employee_code))
from company a 
join employee e on e.company_code =a.company_code
group by a.company_code,a.founder
order by a.company_code asc;

Thank you

The above is mine while below is example. If anyone kind to explain why mine not working, that would be helpful

Sorry: I realised I didn’t put expectaction;