I have several tables, given below:
Company:
| company_code | ceo |
|---|---|
| "C1" | "John" |
| "C2" | "Andrew" |
Lead:
| lead_code | company_code |
|---|---|
| "LM1" | "C1" |
| "LM2" | "C2" |
Senior:
| lead_code | company_code | Senior_code |
|---|---|---|
| "LM1" | "C1" | "SM1" |
| "LM1" | "C1" | "SM2" |
| "LM2" | "C2" | "SM3" |
Manager:
| lead_code | company_code | Senior_code | Manager_code |
|---|---|---|---|
| "LM1" | "C1" | "SM1" | "M1" |
| "LM2" | "C2" | "SM3" | "M2" |
| "LM2" | "C2" | "SM3" | "M3" |
Employee:
| lead_code | company_code | Senior_code | Manager_code | Employee_Code |
|---|---|---|---|---|
| "LM1" | "C1" | "SM1" | "M1" | "E1" |
| "LM1" | "C1" | "SM1" | "M1" | "E2" |
| "LM2" | "C2" | "SM3" | "M2" | "E3" |
| "LM2" | "C2" | "SM3" | "M3" | "E4" |
I want to print out CEO, total number of leads, seniors, managers and employees grouped by the company code. My output should look like this:
O/P:
C1 John 1 2 1 2
C2 Andrew 1 1 2 2
How should I approach this?
>Solution :
You can aggregate first, and then join with the main table. For example:
select
c.*, l.c, s.c, m.c, e.c
from company c
left join (select company_code, count(*) as c from lead group by company_code) l on l.company_code = c.company_code
left join (select company_code, count(*) as c from senior group by company_code) s on s.company_code = c.company_code
left join (select company_code, count(*) as c from manager group by company_code) m on m.company_code = c.company_code
left join (select company_code, count(*) as c from employee group by company_code) e on e.company_code = c.company_code