Why you are using all four tables..We can get data by using only two tabbles Company and Employee..Please correct me if I am wrong... My solution for Oracle : /* Enter your query here. Please append a semicolon ";" at the end of the query and enter your query in a single line to avoid error. */ SELECT company_code, founder,COUNT_EC,COUNT_MC,COUNT_SMC,COUNT_LMC FROM ( SELECT company_code, founder, COUNT(DISTINCT(employee_code)) OVER (PARTITION BY founder) AS COUNT_EC, COUNT(DISTINCT(manager_code)) OVER (PARTITION BY founder)AS COUNT_MC, COUNT(DISTINCT(senior_manager_code)) OVER (PARTITION BY founder)AS COUNT_SMC, COUNT(DISTINCT(lead_manager_code)) OVER (PARTITION BY founder)AS COUNT_LMC FROM( SELECT employee_code,manager_code,senior_manager_code, lead_manager_code,Employee.company_code AS company_code ,Company.founder AS founder FROM EMPLOYEE JOIN Company ON(Employee.company_code=Company.company_code))) GROUP BY company_code, founder,COUNT_EC,COUNT_MC,COUNT_SMC,COUNT_LMC ORDER BY company_code;