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?

CodePudding user response：

SELECT company_code, ceo, COUNT(DISTINCT lead_code) as total_lead, COUNT(DISTINCT Senior_code) as total_senior, COUNT(DISTINCT Manager_code) as total_manager, COUNT(DISTINCT Employee_Code) as total_employee
FROM
(
SELECT Company.company_code, ceo, Lead.lead_code,  Senior.Senior_code, Manager.Manager_code, Employee.Employee_Code
FROM Company
LEFT JOIN Lead
ON Lead.company_code = Company.company_code
LEFT JOIN Senior
ON Senior.company_code = Company.company_code
LEFT JOIN Manager
ON Manager.company_code = Company.company_code
LEFT JOIN Employee
ON Employee.company_code = Company.company_code
)
GROUP BY company_code, ceo;

CodePudding user response：

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