Sort by

recency

|

3291 Discussions

|

  • + 0 comments

    I know there are simpler ways to solve this problem, but I want to figure it out using a non-correlated subquery, because I've just studied it.

    My solution in MySQL:

    SELECT
        c.company_code,
        c.founder,
        (SELECT COUNT(DISTINCT lead_manager_code) FROM lead_manager l WHERE l.company_code = c.company_code) AS total_lead_managers,
        (SELECT COUNT(DISTINCT senior_manager_code) FROM senior_manager s WHERE s.company_code = c.company_code) AS total_senior_managers,
        (SELECT COUNT(DISTINCT manager_code) FROM manager m WHERE m.company_code = c.company_code) AS total_managers,
        (SELECT COUNT(DISTINCT employee_code) FROM employee e WHERE e.company_code = c.company_code) AS total_employee
    FROM company c 
    ORDER BY c.company_code ASC 
    
  • + 0 comments

    there is no need to use all the tables. All the components are present in the employee table except founders names. so we can just use the company table and the employee table here's my code: SELECT c.company_code, c.founder, COUNT(DISTINCT e.lead_manager_code ) AS total_lm, COUNT(DISTINCT e.senior_manager_code ) AS total_sm, COUNT(DISTINCT e.manager_code ) AS total_m, COUNT(DISTINCT e.employee_code ) AS total_e FROM company c LEFT JOIN employee e ON c.company_code=e.company_code GROUP BY c.company_code, c.founder ORDER BY c.company_code;

  • + 0 comments

    My solution

    SELECT company.company_code, company.founder, (SELECT COUNT(DISTINCT lead_manager_code) FROM lead_manager WHERE lead_manager.company_code = company.company_code) AS total_lead_manager, (SELECT COUNT(DISTINCT senior_manager_code) FROM senior_manager WHERE senior_manager.company_code = company.company_code) AS total_senior_manager, (SELECT COUNT(DISTINCT manager_code) FROM manager WHERE manager.company_code = company.company_code) AS total_manager, (SELECT COUNT(DISTINCT employee_code) FROM employee WHERE employee.company_code = company.company_code) AS total_employee FROM company ORDER BY company.company_code ASC;

  • + 1 comment

    Ms sql , No need to use all tables

    select company.company_code , founder, 
    count(distinct lead_manager_code) ,
    count(distinct senior_manager_code),
    count(distinct manager_code),
    
    count(distinct employee_code)
    
     from company 
    left join employee on employee.company_code = company.company_code
    group by company.company_code ,founder
    
  • + 0 comments

    in MS sql

    with cte_emp as ( select company_code,count(distinct lead_manager_code) as lmc,count(distinct senior_manager_code) as smc, count(distinct manager_code) as mc,count(distinct employee_code) as ec from employee group by company_code )select a.company_code,b.founder,a.lmc,a.smc,a.mc,a.ec from cte_emp a join company b on a.company_code = b.company_code order by a.company_code