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.
Very nice. I like that you didn't use any joins here, because this section implies that you should use "Advanced Select" technique rather than "Advanced Join" technique. Your solution is working well for MSSQL as well (though there is one more group by needed for selecting c.founder). The version in T-SQL I came up with:
Hi the reason we have used the founders name in the groupby clause is because if you see the select statement then you see that we have performed aggregate function of COUNT on 4 things and 2 which are the company code and the founder name do not have any aggregate function. so we know according to rules of groupby that either it has to have an aggregate function or it must be included in the GROUP by clause and hence we used the company code and the founder name in the GROUP BY clause.
I agree with you and @kimbaudi that matching on the manager and employee codes does not work if there are managers without subordinates and that it is necessary to match on company_code everywhere. I also believe that yours ist the most correct (if there is such a thing) solution when it comes to best practices.
However since the question seems to be looking for a solution without joins, here is my solution using the implicit join via WHERE proposed by @karthiksk and @mialkin:
Managers by definition manage people, and require people to be a manager, otherwise they are not a manager - this could be a real rule. Covering the case in which a manager doesn't have subordinates is reasonable, but not tested for so therefore it is not required.
Hi
Why did we join the tables using the company code and not the other codes such as lead manager code to join the lead manager table and senior manager table?
The version I wrote was having Oracle in mind but for joins used more of ANSI-Standard .
Your code was very similar to the one I wrote except it is hard to read with that non-ANSI sytanx used in the join conditions .
Hi, thank you for the clean code. I am struggling to understand why do we need to use both columns (C.company_code, C.founder) in Group by function? Really appreciate any advice. Thanks!
select C.company_code,
C.founder,
COUNT(DISTINCT l.lead_manager_code),
COUNT(DISTINCT s.senior_manager_code),
COUNT(DISTINCT m.manager_code),
COUNT(DISTINCT E.employee_code)
from Company C
left join lead_manager l on c.company_code=l.company_code
left join senior_manager s on l.company_code=s.company_code
left join manager m on s.company_code=m.company_code
left join Employee E ON m.company_code = E.company_code
GROUP BY C.company_code, C.founder
ORDER BY C.company_code ASC;
where emp.company_code = mgr.company_code AND
mgr.company_code = srMgr.company_code AND
srMgr.company_code = leadMgr.company_code AND
leadMgr.company_code = comp.company_code
group by comp.company_code
comp.company_code
comp.company_code, comp.founder
order by comp.company_code
comp.company_code
comp.company_code;
COUNT(Distinct srMgr.senior_manager_code), COUNT(Distinct mgr.manager_code), COUNT(Distinct emp.employee_code) from
Employee emp, Manager mgr, Senior_Manager srMgr, Lead_Manager leadMgr , Company comp
where emp.company_code = mgr.company_code AND mgr.company_code = srMgr.company_code AND srMgr.company_code =
leadMgr.company_code AND leadMgr.company_code = comp.company_code
group by comp.company_code , comp.founder order by comp.company_code;
Correct CODE, Seems like it was copay past mistake:
SELECT comp.company_code, comp.founder, COUNT(Distinct leadMgr.lead_manager_code), COUNT(Distinct srMgr.senior_manager_code), COUNT(Distinct mgr.manager_code), COUNT(Distinct emp.employee_code) from Employee emp, Manager mgr, Senior_Manager srMgr, Lead_Manager leadMgr , Company comp
where emp.company_code = mgr.company_code AND mgr.company_code = srMgr.company_code AND srMgr.company_code = leadMgr.company_code AND leadMgr.company_code = comp.company_code
group by comp.company_code, comp.founder order by comp.company_code comp.company_code comp.company_code;
What do you mean it doesn't use any joins? Just because the word "join" isn't present doesn't mean there aren't joins. There are other ways to join, and "c.company_code = l.company_code" is an example that works in MySQL (and Oracle).
select E.company_code,C.founder,
count(distinct LM.lead_manager_code),count(distinct SM.senior_manager_code),
count(distinct M.manager_code),count(distinct E.employee_code)
from
Company C Join Lead_Manager LM on C.company_code=LM.company_code
Left Join
Senior_Manager SM on LM.lead_manager_code=SM.lead_manager_code
Left Join
Manager M on SM.senior_manager_code=M.senior_manager_code
Left Join
Employee E on M.manager_code=E.manager_code
group by
E.company_code,C.founder
I see that this code works but why should one use e.company_code in the select statement versus c.company_code.
Also what is wrong if we join the tables using the company_code. I would appreciate if you could help me understand the logic.
Actually, it doesn't matter that you use e.company_code or c.company_code. The only difference is that you only GROUP BY c.company_code, but he GROUP BY c.company_code, c.founder. Try to add c.founder in GROUP BY clause, it should work. :)
I have given both company_code and founder in group by clause .but, still the result is wrong. Could you please explain why its not working when we join using company code.
SELECT c.company_code,
c.founder,
count(distinct lm.lead_manager_code),
count(distinct sm.senior_manager_code),
count(distinct m.manager_code),
count(distinct e.employee_code)
FROM company c
join lead_manager lm
on c.company_code = lm.company_code
join senior_manager sm
on lm.company_code = sm.company_code
join manager m
on sm.company_code = m.company_code
join employee e
on m.company_code = e.company_code
GROUP BY c.company_code,c.founder
order by c.company_code;
I ran into this issue - Wrote a pretty much identical query to his and ran into this problem. Here was my original code:
SELECT C.Company_Code,
C.Founder,
COUNT(DISTINCT L.Lead_Manager_Code),
COUNT(DISTINCT S.Senior_Manager_Code),
COUNT(DISTINCT M.Manager_Code),
COUNT(DISTINCT E.Employee_Code)
FROM Company C,
Lead_Manager L,
Senior_Manager S,
Manager M,
Employee E
WHERE C.Company_Code = L.Company_Code
AND L.Lead_Manager_Code = S.Lead_Manager_Code
AND S.Senior_Manager_Code = M.Senior_Manager_Code
AND M.Manager_Code = E.Manager_Code
GROUP BY C.Company_Code
ORDER BY C.Company_Code;
As you can see all the selected columns are aggregate functions except for the first two. Since we are grouping by Company Code, it reduces the entries for that code to one row so it can match aggregates like COUNT(). But if we're not grouping by Founder, we will still end up with multiple entries since we've only selected Founder.
Let me know if that makes sense. It's just my beginner-level understanding of it.
Because all columns in the Select statement must be part of the Group By else it wont work.
In this case we are diplaying COMPANY_CODE,FOUNDER followed by aggregate funtions. Hence Group By must always contain COMPANY_CODE,FOUNDER else it will show
Msg 8120, Level 16, State 1, Server WIN-ILO9GLLB9J0, Line 2
Column 'COMPANY.founder' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
WITH C AS(
SELECT COMPANY_CODE,FOUNDER FROM COMPANY ORDER BY COMPANY_CODE),
E AS(
SELECT COMPANY_CODE,COUNT(DISTINCT(EMPLOYEE_CODE)) AS ECNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE),
M AS(
SELECT COMPANY_CODE,COUNT(DISTINCT(MANAGER_CODE)) AS MCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE),
S AS(
SELECT COMPANY_CODE,COUNT(DISTINCT(SENIOR_MANAGER_CODE)) AS SCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE),
L AS(
SELECT COMPANY_CODE,COUNT(DISTINCT(LEAD_MANAGER_CODE)) AS LCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE)
SELECT C.COMPANY_CODE,C.FOUNDER,LCNT,SCNT,MCNT,ECNT FROM C
INNER JOIN L ON C.COMPANY_CODE = L.COMPANY_CODE
INNER JOIN S ON L.COMPANY_CODE = S.COMPANY_CODE
INNER JOIN M ON S.COMPANY_CODE = M.COMPANY_CODE
INNER JOIN E ON M.COMPANY_CODE = E.COMPANY_CODE
ORDER BY C.COMPANY_CODE;
You're using the Employee table to count all employees and managers. what if there is a manager that doesn't have an employee? it woudn't be anywhere in the Employee table.
You are then assuming that a Manager is not an employee of the organization. By virtue, shouldn't all employees irrespective of the fact that they are an employee/manager/senior manager/lead manager/founder;be an employee of the organization first?
Agreed, but you are preparing for an interview, it's risky to just assume the data is trustworthy. I'd advise validating those assumptions with your interviewer.
This is a way better/more efficient solution than the previously sugested "4 joins", given employee has complete data.
you are doing it wrong. Only taking account of "Employee" may give currect result in some scenario, but in case if there is a manager who does not have an employee under him, how would you count him ,because his record will not be there in "Employee" table
Using commas after a FROM statement implicitly creates an INNER JOIN, so even though you don't see the word 'join', this solution is still utilizing joins.
No need to select from all the tables...Company & Employee Tables are enough.
SELECT C.COMPANY_CODE, C.FOUNDER, COUNT(DISTINCT E.LEAD_MANAGER_CODE), COUNT(DISTINCT E.SENIOR_MANAGER_CODE), COUNT(DISTINCT E.MANAGER_CODE), COUNT(DISTINCT E.EMPLOYEE_CODE)
FROM COMPANY C
JOIN EMPLOYEE E ON E.COMPANY_CODE = C.COMPANY_CODE
GROUP BY C.COMPANY_CODE, C.FOUNDER
ORDER BY COMPANY_CODE;
select c.company_code, c.founder,
count(distinct e.lead_manager_code ),
count(distinct e.senior_manager_code),
count(distinct e.manager_code),
count(distinct e.employee_code) from employee e
inner join company c on e.company_code = c.company_code
group by c.company_code, c.founder
order by c.company_code, c.founder;
ERROR 1055 (42000) at line 1: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_zlfpar3yv3e.c.founder' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
What???? what did you mean by "does not use any join", it does use a join although it is more of oracle style rather than ANSI-Standard.
I highly recomend newbies to use "ANSI" standard, this would be one way solving this question:
select * from(
select company_code,
founder,
count(distinct lead_manager_code),
count(distinct senior_manager_code),
count(distinct manager_code),
count(distinct employee_code)
from (select distinct c.company_code,
c.founder,
l.lead_manager_code,
s.senior_manager_code,
m.manager_code,
em.employee_code
from company c
inner join lead_manager l on (c.company_code = l.company_code)
inner join senior_manager s on (s.company_code = l.company_code
and l.lead_manager_code = s.lead_manager_code)
inner join manager m on (m.senior_manager_code=s.senior_manager_code and m.lead_manager_code=s.lead_manager_code)
inner join employee em on (em.manager_code = m.manager_code)
)
group by company_code,
founder)
order by company_code;
when you select from multiple tables, SQL does a cross join anyway. So you're actually not avoiding joins, you're cross joining and then filtering the correct rows(emulating a conditional join)...anyway..heres my mainstream solution :p
1.select z.company_code, z.founder, z.lm_count, z.sm_count,z.m_count,count(distinct(employee_code)) as e_code
from Employee as e
join
(select b.company_code, b.founder, b.lm_count, b.sm_count, count(distinct(manager_code)) as m_count
from manager as m
join
(select a.company_code, a.founder, a.lm_count, count(distinct(senior_manager_code)) as sm_count
from
(select c.company_code, c.founder, count(distinct(lm.lead_manager_code)) as lm_count
from company as c
join lead_manager as lm
on c.company_code = lm.company_code
group by c.company_code,c.founder) as a
join senior_manager as sm
on a.company_code = sm.company_code
group by a.company_code, a.founder, a.lm_count) as b
on m.company_code = b.company_code
from Company c, Lead_Manager lm, Senior_Manager sm, Manager m, Employee e
where c.company_code = lm.company_code
and lm.lead_manager_code = sm.lead_manager_code
and sm.senior_manager_code = m.senior_manager_code
and m.manager_code = e.manager_code
group by c.company_code, c.founder
order by c.company_code
2 is a much more optimized code and there are no subqueries. Can someone please explain to me the constraints b/w 1 and 2 i.e. how one is constrained in respect to 2?
group by b.company_code, b.founder, b.lm_count, b.sm_count) as z
on z.company_code = e.company_code
group by z.company_code, z.founder, z.lm_count, z.sm_count,z.m_count
order by z.company_code
You did not understand my question. It isn't about whether joins are being used or not. Before you call out my concepts I think you should work on your English vocabulary. :P
I have used inner joins because I think the select query is more complicate and hard to read.
SELECT C.company_code,C.founder,count(distinct l.lead_manager_code),count(distinct s.senior_manager_code),count(distinct m.manager_code),count(distinct e.employee_code) from Company C
INNER JOIN Lead_Manager L ON L.company_code=C.company_code
INNER JOIN Senior_Manager S ON S.company_code=C.company_code AND S.lead_manager_code=L.lead_manager_code
INNER JOIN Manager M ON M.company_code=C.company_code AND M.senior_manager_code=S.senior_manager_code
INNER JOIN Employee E ON E.company_code=C.company_code AND E.manager_code=M.manager_code
group by c.company_code, c.founder
order by c.company_code
SQL Server will transform and optimize your query before executing it anyways, so there will be no difference between explicit joins and doing a join in WHERE clause... Some would also argue that explicit joins are more readable.
SELECT c.company_code,
c.founder,
COUNT(DISTINCT e.lead_manager_code),
COUNT(DISTINCT e.senior_manager_code),
COUNT(DISTINCT e.manager_code),
COUNT(DISTINCT e.employee_code)
FROM Employee e, Company c
WHERE c.company_code = e.company_code
GROUP BY e.company_code, c.founder
ORDER BY c.company_code;
ERROR 1055 (42000) at line 4: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_s86u5zedvw8.C.founder' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Can't we use only two tables to get desired output
select c.company_code, founder,count(distinct lead_manager_code),count(distinct senior_manager_code),count(distinct manager_code),count(distinct employee_code) from Company c inner join Employee e on c.company_code=e.company_code group by c.company_code,founder order by c.company_code
This query is still a join. It's just that the syntax is not standard. There is no difference between this and inner join. All the join is being done in the where clause.
Hey hi, thank you for the answer.
I am wondering why you would need to go through so many tables?
Doesn't the employee table have all the columns you need?
Can't we just join Company, Employee on company_code and count distinct after grouping by company_code.
I understand that you did not wish to join anything but aren't you just doing the implicit cartesian product? That is also a form of join no?
Oh my... I didn't even notice it actually mentioned category "Advance Select" on top navigation, so I had used left join instead. I'm glad to read this section.
c.company_code = lm.company_code
and lm.lead_manager_code = sm.lead_manager_code
and sm.senior_manager_code = m.senior_manager_code
and m.manager_code = e.manager_code
I also use 2 tables only. Here is my solution for MySQL
select c.company_code, c.founder, count(distinct e.lead_manager_code), count(distinct e.senior_manager_code), count(distinct e.manager_code), count(distinct e.employee_code)
from company as c join employee as e on c.company_code = e.company_code
group by c.company_code, c.founder
order by company_code
New Companies
You are viewing a single comment's thread. Return to all comments →
Very nice. I like that you didn't use any joins here, because this section implies that you should use "Advanced Select" technique rather than "Advanced Join" technique. Your solution is working well for MSSQL as well (though there is one more group by needed for selecting c.founder). The version in T-SQL I came up with:
@mialkin - although your solution passes all test cases, it is actually incorrect. For the problem sample input, your solution would actually be:
C1 Monika 1 1 1 2
C2 Samantha 1 1 2 2
The correct solution to the sample input should be:
C1 Monika 1 2 1 2
C2 Samantha 1 1 2 2
Company C1 founded by Monika should have 2 senior managers (SM1 and SM2).
The key column to check is company_code. SQL SERVER:
select cp.company_code, cp.founder , count(distinct lm.lead_manager_code) lead_manager_code, count(distinct sm.senior_manager_code) senior_manager_code , count(distinct mn.manager_code) manager_code, count(distinct ep.employee_code) employee_code from Company cp join Lead_Manager lm on lm.company_code = cp.company_code join Senior_Manager sm on sm.company_code = lm.company_code join Manager mn on mn.company_code = sm.company_code join Employee ep on ep.company_code = mn.company_code
group by cp.company_code, cp.founder order by cp.company_code asc;
can you explain why there was a need to group it by founder ?
Hi the reason we have used the founders name in the groupby clause is because if you see the select statement then you see that we have performed aggregate function of COUNT on 4 things and 2 which are the company code and the founder name do not have any aggregate function. so we know according to rules of groupby that either it has to have an aggregate function or it must be included in the GROUP by clause and hence we used the company code and the founder name in the GROUP BY clause.
Thank you!
I agree with you and @kimbaudi that matching on the manager and employee codes does not work if there are managers without subordinates and that it is necessary to match on
company_codeeverywhere. I also believe that yours ist the most correct (if there is such a thing) solution when it comes to best practices.However since the question seems to be looking for a solution without joins, here is my solution using the implicit join via
WHEREproposed by @karthiksk and @mialkin:Managers by definition manage people, and require people to be a manager, otherwise they are not a manager - this could be a real rule. Covering the case in which a manager doesn't have subordinates is reasonable, but not tested for so therefore it is not required.
Hi Why did we join the tables using the company code and not the other codes such as lead manager code to join the lead manager table and senior manager table?
clear code for MYSQL is here. https://github.com/mremreozan/SQL/blob/master/Hackerrank/995%20New%20Companies
The version I wrote was having Oracle in mind but for joins used more of ANSI-Standard . Your code was very similar to the one I wrote except it is hard to read with that non-ANSI sytanx used in the join conditions .
eline sağlık :)
Hi, thank you for the clean code. I am struggling to understand why do we need to use both columns (C.company_code, C.founder) in Group by function? Really appreciate any advice. Thanks!
Yep. 2 columns seems not necessary.
Please let me know why do we need to use both columns (C.company_code, C.founder) in Group by function?
Because we are performing the aggregate function COUNT().
If we in TSql perform any aggregates, we need to group anything that is not calculated, into the group by clause.
the right code for this would be-
select C.company_code, C.founder, COUNT(DISTINCT l.lead_manager_code), COUNT(DISTINCT s.senior_manager_code), COUNT(DISTINCT m.manager_code), COUNT(DISTINCT E.employee_code) from Company C left join lead_manager l on c.company_code=l.company_code left join senior_manager s on l.company_code=s.company_code left join manager m on s.company_code=m.company_code left join Employee E ON m.company_code = E.company_code GROUP BY C.company_code, C.founder ORDER BY C.company_code ASC;
thanks
I wrote the same
why you used group by c.founder
Thanks for pointing out the problem. I didn't notice!
why cant we use? lm.company_code = sm.comapny_code and sm.company_code = m.company_code and m.company_code = e.company_code
yes this also works ,see my query below
SELECT comp.company_code comp.company_code comp.company_code, comp.founder, COUNT(Distinct leadMgr.lead_manager_code), COUNT(Distinct srMgr.senior_manager_code), COUNT(Distinct mgr.manager_code), COUNT(Distinct emp.employee_code) from Employee emp, Manager mgr, Senior_Manager srMgr, Lead_Manager leadMgr , Company comp
where emp.company_code = mgr.company_code AND mgr.company_code = srMgr.company_code AND srMgr.company_code = leadMgr.company_code AND leadMgr.company_code = comp.company_code
group by comp.company_code comp.company_code comp.company_code, comp.founder order by comp.company_code comp.company_code comp.company_code;
your code showing below mentioned error.
Msg 102, Level 15, State 1, Server WIN-ILO9GLLB9J0, Line 2 Incorrect syntax near '.'.
SELECT comp.company_code,comp.founder,COUNT(Distinct(leadMgr.lead_manager_code)),
COUNT(Distinct srMgr.senior_manager_code), COUNT(Distinct mgr.manager_code), COUNT(Distinct emp.employee_code) from Employee emp, Manager mgr, Senior_Manager srMgr, Lead_Manager leadMgr , Company comp
where emp.company_code = mgr.company_code AND mgr.company_code = srMgr.company_code AND srMgr.company_code = leadMgr.company_code AND leadMgr.company_code = comp.company_code
group by comp.company_code , comp.founder order by comp.company_code;
Correct CODE, Seems like it was copay past mistake:
SELECT comp.company_code, comp.founder, COUNT(Distinct leadMgr.lead_manager_code), COUNT(Distinct srMgr.senior_manager_code), COUNT(Distinct mgr.manager_code), COUNT(Distinct emp.employee_code) from Employee emp, Manager mgr, Senior_Manager srMgr, Lead_Manager leadMgr , Company comp where emp.company_code = mgr.company_code AND mgr.company_code = srMgr.company_code AND srMgr.company_code = leadMgr.company_code AND leadMgr.company_code = comp.company_code group by comp.company_code, comp.founder order by comp.company_code comp.company_code comp.company_code;
The senior manager sm2 has no employee but it must be counted as senior manager.so you have to use left join
I thought that 'from Company c, Lead_Manager l, Senior_Manager s, Manager m, Employee e' is so called Cross-Join operation.
it is a type of join!
What do you mean it doesn't use any joins? Just because the word "join" isn't present doesn't mean there aren't joins. There are other ways to join, and "c.company_code = l.company_code" is an example that works in MySQL (and Oracle).
Exactly, he just didn't use the keyword 'join' and AFAIK, there seems to be no difference at all. See here
could please tell me why it doesn't work with join in my code? It tells me Unknown column 'c.fonder' in 'field list'
founder* company_code*
wow thanks
but it still doesnt work....
Try this out, it worked for me
select E.company_code,C.founder, count(distinct LM.lead_manager_code),count(distinct SM.senior_manager_code), count(distinct M.manager_code),count(distinct E.employee_code) from Company C Join Lead_Manager LM on C.company_code=LM.company_code Left Join Senior_Manager SM on LM.lead_manager_code=SM.lead_manager_code Left Join Manager M on SM.senior_manager_code=M.senior_manager_code Left Join Employee E on M.manager_code=E.manager_code group by E.company_code,C.founder
I see that this code works but why should one use e.company_code in the select statement versus c.company_code. Also what is wrong if we join the tables using the company_code. I would appreciate if you could help me understand the logic.
Actually, it doesn't matter that you use e.company_code or c.company_code. The only difference is that you only GROUP BY c.company_code, but he GROUP BY c.company_code, c.founder. Try to add c.founder in GROUP BY clause, it should work. :)
I have given both company_code and founder in group by clause .but, still the result is wrong. Could you please explain why its not working when we join using company code.
can someone please tell me, select E.company_code,C.founder why u used E in E.company_code instead of C.company_code?
group by founder also
Thanks, this is what I was missing!
Add group by founder too.
group by c.company_code, c.founder order by c.company_code.
finally I added another one in the group by clause
why do you need c.founder
because in GROUP BY you need to add all expression used in select except the agreegation expression like count(distinct lm.lead_manager_code).
Very good explanation! thanks
thankyou for your explanation
Holy typo
Shouldnt there be 'distinct' in after each count?
You need to get the distinct count of all the code
try this
SELECT c.company_code, c.founder, count(distinct lm.lead_manager_code), count(distinct sm.senior_manager_code), count(distinct m.manager_code), count(distinct e.employee_code) FROM company c join lead_manager lm on c.company_code = lm.company_code join senior_manager sm on lm.company_code = sm.company_code join manager m on sm.company_code = m.company_code join employee e on m.company_code = e.company_code GROUP BY c.company_code,c.founder order by c.company_code;
"group by c.company_code,c.founder_name" should be added i.e founder name also needs to be grouped.
Hi @malkin, Please don't confuse... "I like that you didn't use any joins here"
What are these if not joins??
Alliases
Thank you , but can you explain bit more . I could not understand .Thank you frk_srknt :)
SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of the query.
It makes easier to read and write queries but I'm not sure If you asked this part of code.
The aliases are still JOINed, but without using the JOIN keyword specifically here. Those commas are indeed implicit JOIN operatorss
mialkin Why adding another group by i.e c.founder?
I ran into this issue - Wrote a pretty much identical query to his and ran into this problem. Here was my original code:
As you can see all the selected columns are aggregate functions except for the first two. Since we are grouping by Company Code, it reduces the entries for that code to one row so it can match aggregates like COUNT(). But if we're not grouping by Founder, we will still end up with multiple entries since we've only selected Founder.
Let me know if that makes sense. It's just my beginner-level understanding of it.
why is c.founder needed here? My output isn't coming without c.founder.
Because all columns in the Select statement must be part of the Group By else it wont work.
In this case we are diplaying COMPANY_CODE,FOUNDER followed by aggregate funtions. Hence Group By must always contain COMPANY_CODE,FOUNDER else it will show
Msg 8120, Level 16, State 1, Server WIN-ILO9GLLB9J0, Line 2 Column 'COMPANY.founder' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Hope this answers your question.
He did use joins but an EXPLICIT one.
WITH C AS( SELECT COMPANY_CODE,FOUNDER FROM COMPANY ORDER BY COMPANY_CODE), E AS( SELECT COMPANY_CODE,COUNT(DISTINCT(EMPLOYEE_CODE)) AS ECNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE), M AS( SELECT COMPANY_CODE,COUNT(DISTINCT(MANAGER_CODE)) AS MCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE), S AS( SELECT COMPANY_CODE,COUNT(DISTINCT(SENIOR_MANAGER_CODE)) AS SCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE), L AS( SELECT COMPANY_CODE,COUNT(DISTINCT(LEAD_MANAGER_CODE)) AS LCNT FROM EMPLOYEE GROUP BY COMPANY_CODE ORDER BY COMPANY_CODE) SELECT C.COMPANY_CODE,C.FOUNDER,LCNT,SCNT,MCNT,ECNT FROM C INNER JOIN L ON C.COMPANY_CODE = L.COMPANY_CODE INNER JOIN S ON L.COMPANY_CODE = S.COMPANY_CODE INNER JOIN M ON S.COMPANY_CODE = M.COMPANY_CODE INNER JOIN E ON M.COMPANY_CODE = E.COMPANY_CODE ORDER BY C.COMPANY_CODE;
Actually, Karthiksk and you both use a join, an implicit one in the where clause.
The tables are joined using an (=) sign, its called equi join or natural join because of (=) operator.
Here's another way where you don't use an where and have just one join with the Employee table as all data that we need are present over there.
Looks neat. Thanks for sharing.
You're using the Employee table to count all employees and managers. what if there is a manager that doesn't have an employee? it woudn't be anywhere in the Employee table.
You are then assuming that a Manager is not an employee of the organization. By virtue, shouldn't all employees irrespective of the fact that they are an employee/manager/senior manager/lead manager/founder;be an employee of the organization first?
Agreed, but you are preparing for an interview, it's risky to just assume the data is trustworthy. I'd advise validating those assumptions with your interviewer. This is a way better/more efficient solution than the previously sugested "4 joins", given employee has complete data.
good answer
Why need group by C.company_code , C.founder? I think group by C.company_code is enough, but it is wrong. Why?
Actually the rule says that group by statement must include all the attributes in the select command other than aggeregate functions.
you are doing it wrong. Only taking account of "Employee" may give currect result in some scenario, but in case if there is a manager who does not have an employee under him, how would you count him ,because his record will not be there in "Employee" table
right
practically,will there be managers without employees under him?
A manager IS an Employee first
Good point, thanks. A case of solving the immediate, but not solving the general.
yes actually this rule apply only to the case where we are using aggregate functions in select statements
my code is same but without "Group by C.founder" and it comming wrong.
Can you help to explain why we need to group the founder also in this case ? Thanks
Actually the rule says that group by statement must include all the attributes in the select command other than aggeregate functions.
Using commas after a FROM statement implicitly creates an INNER JOIN, so even though you don't see the word 'join', this solution is still utilizing joins.
This response's first remark is misguided. Listing several table names after each other is the equivalent of an explicit cross join.
This is a join operation after all
maybe this one would be more easy:
select e.company_code, c.founder, count(distinct lead_manager_code), count(distinct senior_manager_code), count(distinct manager_code), count(distinct employee_code)
from Company c left outer join Employee e ON e.company_code=c.company_code group by e.company_code, c.founder order by e.company_code
It is just like a join if you are using Select * from tab1,tab2 where tab1.id=tab2.id
not using the join word does not mean you are not using it.
Using multiple tables in FROM and using WHERE conditions is basically a join
No need to select from all the tables...Company & Employee Tables are enough.
SELECT C.COMPANY_CODE, C.FOUNDER, COUNT(DISTINCT E.LEAD_MANAGER_CODE), COUNT(DISTINCT E.SENIOR_MANAGER_CODE), COUNT(DISTINCT E.MANAGER_CODE), COUNT(DISTINCT E.EMPLOYEE_CODE) FROM COMPANY C JOIN EMPLOYEE E ON E.COMPANY_CODE = C.COMPANY_CODE GROUP BY C.COMPANY_CODE, C.FOUNDER ORDER BY COMPANY_CODE;
That has been done with the help of equi join.
Why we don't need to add founder in group by clause in this version, but need if we use join?
select c.company_code, c.founder, count(distinct e.lead_manager_code ), count(distinct e.senior_manager_code), count(distinct e.manager_code), count(distinct e.employee_code) from employee e inner join company c on e.company_code = c.company_code group by c.company_code, c.founder order by c.company_code, c.founder;
my idea
Working as a charm
Hi Guys, This code shows this error. What to do?
ERROR 1055 (42000) at line 1: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_zlfpar3yv3e.c.founder' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
What???? what did you mean by "does not use any join", it does use a join although it is more of oracle style rather than ANSI-Standard. I highly recomend newbies to use "ANSI" standard, this would be one way solving this question:
select * from( select company_code, founder, count(distinct lead_manager_code), count(distinct senior_manager_code), count(distinct manager_code), count(distinct employee_code) from (select distinct c.company_code, c.founder, l.lead_manager_code, s.senior_manager_code, m.manager_code, em.employee_code from company c inner join lead_manager l on (c.company_code = l.company_code) inner join senior_manager s on (s.company_code = l.company_code and l.lead_manager_code = s.lead_manager_code) inner join manager m on (m.senior_manager_code=s.senior_manager_code and m.lead_manager_code=s.lead_manager_code) inner join employee em on (em.manager_code = m.manager_code) ) group by company_code, founder) order by company_code;
when you select from multiple tables, SQL does a cross join anyway. So you're actually not avoiding joins, you're cross joining and then filtering the correct rows(emulating a conditional join)...anyway..heres my mainstream solution :p
why did we need to add group by c.founder?
Why do we need c.founder in "group by" operation, why isn't just c.company_code sufficient for grouping?
every column in 'select' needs to be mentioned in the 'group by' clause
more here https://stackoverflow.com/questions/5986127/do-all-columns-in-a-select-list-have-to-appear-in-a-group-by-clause
1.select z.company_code, z.founder, z.lm_count, z.sm_count,z.m_count,count(distinct(employee_code)) as e_code from Employee as e join (select b.company_code, b.founder, b.lm_count, b.sm_count, count(distinct(manager_code)) as m_count from manager as m join (select a.company_code, a.founder, a.lm_count, count(distinct(senior_manager_code)) as sm_count from (select c.company_code, c.founder, count(distinct(lm.lead_manager_code)) as lm_count from company as c join lead_manager as lm on c.company_code = lm.company_code group by c.company_code,c.founder) as a join senior_manager as sm on a.company_code = sm.company_code group by a.company_code, a.founder, a.lm_count) as b on m.company_code = b.company_code
2.select c.company_code, c.founder, count(distinct lm.lead_manager_code), count(distinct sm.senior_manager_code), count(distinct m.manager_code), count(distinct e.employee_code)
from Company c, Lead_Manager lm, Senior_Manager sm, Manager m, Employee e where c.company_code = lm.company_code and lm.lead_manager_code = sm.lead_manager_code and sm.senior_manager_code = m.senior_manager_code and m.manager_code = e.manager_code group by c.company_code, c.founder order by c.company_code
2 is a much more optimized code and there are no subqueries. Can someone please explain to me the constraints b/w 1 and 2 i.e. how one is constrained in respect to 2?
group by b.company_code, b.founder, b.lm_count, b.sm_count) as z on z.company_code = e.company_code group by z.company_code, z.founder, z.lm_count, z.sm_count,z.m_count order by z.company_code
He has used join only. Pls clear your concepts
You did not understand my question. It isn't about whether joins are being used or not. Before you call out my concepts I think you should work on your English vocabulary. :P
Btw for you egghead I presented two queries. I know 2 is more optimized one. I asked where the first query lacks.
hey chicken. what does sql stand for
I won't even grace that question with an answer. Rank dekhle pehle phir baat karte hain. Galti teri hain. Maine kya pucha na samajh ke jawab diya
Is earth oval or round?
You trippin homie?
I have used inner joins because I think the select query is more complicate and hard to read.
SQL Server will transform and optimize your query before executing it anyways, so there will be no difference between explicit joins and doing a join in WHERE clause... Some would also argue that explicit joins are more readable.
More easier version is here.
SELECT c.company_code, c.founder, COUNT(DISTINCT e.lead_manager_code), COUNT(DISTINCT e.senior_manager_code), COUNT(DISTINCT e.manager_code), COUNT(DISTINCT e.employee_code) FROM Employee e, Company c WHERE c.company_code = e.company_code GROUP BY e.company_code, c.founder ORDER BY c.company_code;
can u explain y did u use group by c.company_code, c.founder
Actually, he is using the worst kind of join .... cross joins here.
ERROR 1055 (42000) at line 4: Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'run_s86u5zedvw8.C.founder' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
any idea what to do?
if we group company code then it will automatically group the founders. why it is needed to group founders also?
Can't we use only two tables to get desired output
This query is still a join. It's just that the syntax is not standard. There is no difference between this and inner join. All the join is being done in the where clause.
WHY ARE WE ADDING FOUNDER HERE?
Its actually advanced join only, without using join keyword :)
could you please explain why we need to use group by c.founder
He is using joins. What do you think
does? :)
Hey hi, thank you for the answer. I am wondering why you would need to go through so many tables? Doesn't the employee table have all the columns you need? Can't we just join Company, Employee on company_code and count distinct after grouping by company_code.
I understand that you did not wish to join anything but aren't you just doing the implicit cartesian product? That is also a form of join no?
Can you explain why the additional groupby founder is needed?
Does this solution not assume that every manager will have a senior manager & so on through the entire hierarchy?
Oh my... I didn't even notice it actually mentioned category "Advance Select" on top navigation, so I had used left join instead. I'm glad to read this section.
FROM table1, table2 WHERE x=y...
... this is essentially a JOIN in the old syntax.
... FROM table1, table2... will elicit a CROSS product... which will be "subset-ed" using WHERE.
The comma between the two tables signifies a CROSS JOIN, which gives the Cartesian product of the two tables.
SELECT * FROM T1, T2
is equivalent to:
SELECT * FROM T1 CROSS JOIN T2` so there is still a join in it.
It is still a self cross join I guess...
Can u explain why you have to add c.founder in group clause??
can someone please explain why we need to group by both c.company_code and c.founder??
This is still a join:
c.company_code = lm.company_code and lm.lead_manager_code = sm.lead_manager_code and sm.senior_manager_code = m.senior_manager_code and m.manager_code = e.manager_code
Great Solution Kudos !!
https://learnsql.com/blog/joins-vs-multiple-tables-in-from/
I also use 2 tables only. Here is my solution for MySQL
select c.company_code, c.founder, count(distinct e.lead_manager_code), count(distinct e.senior_manager_code), count(distinct e.manager_code), count(distinct e.employee_code) from company as c join employee as e on c.company_code = e.company_code group by c.company_code, c.founder order by company_code