mysql select count from multiple tables

Question

If I have two tables job, employee. I need to select all from job and append employee count for each job.

Supposed Result

job table
+-------------+-------------+
| id          | name        |
+-------------+-------------+
| 1           | Teacher     |
+-------------+-------------+
| 2           | Engineer    |
+-------------+-------------+
| 3           | Programmer  |
+-------------+-------------+

employee table
+-------------+-------------+-------------+
| id          | name        |   job_id    |
+-------------+-------------+-------------+
| 1           | employee N  |      1      |
+-------------+-------------+-------------+
| 2           | employee N  |      2      |
+-------------+-------------+-------------+
| 3           | employee N  |      3      |
+-------------+-------------+-------------+
| 4           | employee N  |      1      |
+-------------+-------------+-------------+
| 5           | employee N  |      3      |
+-------------+-------------+-------------+
| 6           | employee N  |      1      |
+-------------+-------------+-------------+

I need to select all from job and append employee count for each job.

Supposed Result

Result table
+-------------+-------------+--------------+
| id          | name        |employee count|
+-------------+-------------+--------------+
| 1           | Teacher     |      3       |
+-------------+-------------+--------------+
| 2           | Engineer    |      1       |
+-------------+-------------+--------------+
| 3           | Programmer  |      2       |
+-------------+-------------+--------------+

changec · Accepted Answer · 2014-05-12 07:14:11Z

2

I like to use left join : )

SELECT job.id, job.name, count( * ) AS 'employee count'
FROM job
LEFT JOIN employee 
ON job.id = employee.job_id
GROUP BY job.id

answered May 12, 2014 at 7:14

changec

515 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex.Ritna · Accepted Answer · 2014-05-12 06:59:59Z

1

You want to use INNER JOIN to join the tables, then GROUP BY to group on the job id. Finally use COUNT() to get a count of each group

SELECT job.id, job.name, count(*) AS employee_count
FROM job
INNER JOIN employee 
  ON job.id = employee.job_id
GROUP BY job.id

You can see it live here http://sqlfiddle.com/#!2/9d59c1/1

answered May 12, 2014 at 6:59

Alex.Ritna

1,9842 gold badges17 silver badges26 bronze badges

Collectives™ on Stack Overflow

mysql select count from multiple tables

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related