CODEWITHSUNDEEP
mysqlsql
Gireesh T
Gireesh T

Reputation: 63

How to count repeated entry from reference table?

I have two tables, known as instructor and department. I am struggling to count the number of instructors available for each department. I will attach the screenshot of database tables.

INSTRUCTOR
instructor table

DEPARTMENT
department table

How can we count the number of instructors available in each department using a SQL query?

And also, in the same query, I want to display all the departments.

Is it possible to list all the departments with instructor count using a single query?

Upvotes: 1

Views: 45

Answers (2)

MEdwin
MEdwin

Reputation: 2960

I agree with Vladimir Kovpak. This assumes that some instructors in the instructor table may not be in the department table. If they are all contained in the department table you can use an inner join (change LEFT JOIN to JOIN). And you can actually test Vladimir Kovpak answer using a temp table like this:

--recreate @instructor table
declare @instructor table (instructureid int, instructor_name varchar(10), department_id int, instructor_first_name varchar(10))
insert into @instructor
select 1, 'instr1', 1, 'test1' union all
select 2, 'instr2' , 2, 'test2' union all
select 3, 'instr3', 2, 'test3' 

--recreate @department table
declare @department table (department_id int, department_name varchar(10))
insert into @department
select 1, 'maths' union all
select 2, 'computer'


select dpt.department_id,dpt.department_name, numInst=COUNT(instructureid)
from
    @instructor ins
    LEFT JOIN @department dpt ON
        dpt.department_id=ins.department_id

group by
dpt.department_id,dpt.department_name

Upvotes: 1

cn0047
cn0047

Reputation: 17091

You have to use join and your query must looks like this:

select d.department_id, count(i.instructor_id) as "instructors count"
from department as d
left join instructor as i on d.department_id = i.department_id
group by d.department_id;

Upvotes: 1

Related Questions