CODEWITHSUNDEEP
mysql
dammyspencer
dammyspencer

Reputation: 23

MySQL query to print data from two tables

enter image description here

I need help with writing a MySQL query to print the respective department names and number of Employee for all departments in the Department table.

Expected output:

Executive 2
Technical 2
Production 1

Upvotes: 0

Views: 1220

Answers (3)

David Grable
David Grable

Reputation: 11

A join command would be what you are looking for. 

select transaction.username, transaction.transactiondate, products.price, products.quantity, products.description
from transaction, products
where products.productid = transaction.productid
  and products.productid = IDHERE

Upvotes: 1

PatrickSJ
PatrickSJ

Reputation: 510

SELECT a.name as department_name, count(b.id) as num_of_employees
FROM department a INNER JOIN employee b ON a.dept_id = b.dept_id
GROUP BY a.dept_id

Upvotes: 0

Frank AK
Frank AK

Reputation: 1781

I assume you have a simple table which structure as below show:

+--------+------+
| name   | dep  |
+--------+------+
| frank  | IT   |
| jack   | IT   |
| Sissel | FA   |
| Li     | FA   |
| Mok    | PM   |
+--------+------+

You have three department maybe more, you can simple use count to fetch number of employee for all departments. and if you use group by dep you will get each number by you expect .

SELECT dep, count(*) FROM user_table GROUP BY dep;

And then you got:

+------+----------+
| dep  | count(*) |
+------+----------+
| FA   |        2 |
| IT   |        2 |
| PM   |        1 |
+------+----------+

Hope, that's all your need~

Upvotes: 0

Related Questions