CODEWITHSUNDEEP
phpmysqlcodeigniter
Kirataka
Kirataka

Reputation: 482

How to displays total count of instructors for each department ? SQL Query

Here is the department table

department_id | department_name
1               computers
2               maths
3               physics
4               mba

Here is instructor table

instructor_id | department_id | name
1               1               abc
2               2               manu
3               2               raju
4               3               jaya
5               4               man

From above code how to displays total count of instructors for each department?

what will be the query? Please help.

I'm doing it in codeigniter.

Upvotes: 2

Views: 1881

Answers (3)

Harshil Patel
Harshil Patel

Reputation: 308

SELECT COUNT(i.instructor_id) as `instructor_count`, d.department_name
FROM instructor AS i
JOIN department ON d.department_id = i.department_id
GROUP BY i.department_id"

Upvotes: 1

Omari Victor Omosa
Omari Victor Omosa

Reputation: 2879

You may try to conceptualize from my solution below and see if it can help,

To avoid complication you could just go direct

<?php
//connection
$link = mysqli_connect("localhost","root","","database_name") or die("Couldn't make connection.");

$computer_department_id = //the value
$query = mysqli_query($link, "SELECT  department_id FROM instructure_table WHERE department_id='$computer_department_id')");
$total = mysqli_num_rows($query); 

echo $total; //this one will display the total number of instructors in computer department
?>

Or if you like 

<?php
//connection
$link = mysqli_connect("localhost","root","","database_name") or die("Couldn't make connection.");

$computer_department = //the value
$query = mysqli_query($link, "SELECT  department_id 
FROM instructure_table AS t
WHERE EXISTS (SELECT department_id 
              FROM department_table AS d
              WHERE d.department_id  = t.department_id AND department_id='$computer_department')");
$total = mysqli_num_rows($query); 

echo $total; //this one will display the total number of instructors in computer department
?>

So if you place the query in the department line s 

department_id | department_name
1               computers        //the query
2               maths            //the query
3               physics          //the query
4               mba              //the query

Even if the query for department is in a repeating region it would still work for you. regards

Upvotes: 1

McNets
McNets

Reputation: 10827

select d.department_id, count(*) as num_instructors
from departments d
     inner join instructors i on i.department_id = d.department_id
group by d.department_id;

Upvotes: 2

Related Questions