SQL Challenges-1: Find the Team Size

Last update on December 20 2024 11:58:58 (UTC/GMT +8 hours)

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:

Field	Type	Null	Key
employee_id	int(11)	NO	PRI
first_name	varchar(25)	YES
department_id	int(11)	YES

Data:

employee_id	first_name	department_id
100	Steven	90
101	Neena	90
102	Lex	90
103	Alexander	60
104	Bruce	60
105	David	60
106	Valli	60
107	Diana	60
108	Nancy	100
109	Daniel	100
110	John	100

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.