We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
- Prepare
- SQL
- Advanced Select
- New Companies
- Discussions
New Companies
New Companies
Sort by
recency
|
3291 Discussions
|
Please Login in order to post a comment
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:
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;
My solution
Ms sql , No need to use all tables
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