SQL Challenges-1: Find the Team Size

SQL Challenges-1: Exercise-47 with Solution

From the following table write a SQL query to find the number of employees are working in the department of each employees. Return employee Id and number of employees are working in their department.

Input:

Table: emp_test_table

Structure:

Data:

Sample Solution:

SQL Code(MySQL):

CREATE TABLE emp_test_table (
employee_id integer NOT NULL UNIQUE,
first_name varchar(25),
department_id integer);


insert into emp_test_table values(100,'Steven     ',90); 
insert into emp_test_table values(101,'Neena      ',90); 
insert into emp_test_table values(102,'Lex        ',90); 
insert into emp_test_table values(103,'Alexander  ',60); 
insert into emp_test_table values(104,'Bruce      ',60); 
insert into emp_test_table values(105,'David      ',60); 
insert into emp_test_table values(106,'Valli      ',60); 
insert into emp_test_table values(107,'Diana      ',60); 
insert into emp_test_table values(108,'Nancy      ',100); 
insert into emp_test_table values(109,'Daniel     ',100); 
insert into emp_test_table values(110,'John       ',100); 



SELECT em1.employee_id, COUNT(em1.department_id) AS employees_in_department
FROM emp_test_table em1
JOIN emp_test_table em2
ON em1.department_id=em2.department_id
GROUP BY em1.employee_id;

Sample Output:

employee_id|employees_in_department|
-----------|-----------------------|
        100|                      3|
        101|                      3|
        102|                      3|
        103|                      5|
        104|                      5|
        105|                      5|
        106|                      5|
        107|                      5|
        108|                      3|
        109|                      3|
        110|                      3|

SQL Code Editor:

Contribute your code and comments through Disqus.

Previous: Students and Examinations.
Next: Show running quantiry for each unit type of item.