MySQL COUNT from multiple tables with group

Question

I'm looking to find 2 results from the disciplines table below. The first COUNT would be to see how many Certificates are assigned to each discipline by using the disciplines_certificates table. The second COUNT would be to see how many Workers have the discipline assigned workers_disciplines

I have managed to do individual queries to fetch these answers, but I'm not sure what I need to do in order to get both results in 1 query. (I've just copied over the results with an actual answer to save space, but the end result should include all disciplines.

Query to select the used certificates

SELECT `disciplines`.* , COUNT(disciplines_certificates.certificate_id) as used_certificates
FROM (`disciplines`)
LEFT JOIN `disciplines_certificates` ON `disciplines_certificates`.`discipline_id` = `disciplines`.`id`
GROUP BY `disciplines`.`id`

Result:

+----+-------------------------+-------------------+
| id |         discipline_name | used_certificates |
+----+-------------------------+-------------------+
| 10 |        Crane Op Level 3 |                 3 |
| 18 |        Appointed Person |                 2 |
+----+-------------------------+-------------------+

Query to select the used disciplines

SELECT `disciplines`.*, COUNT(workers_disciplines.discipline_id) as used_disciplines
FROM (`disciplines`)
LEFT JOIN `workers_disciplines` ON `workers_disciplines`.`discipline_id` = `disciplines`.`id`
GROUP BY `disciplines`.`id`

Result:

+----+-------------------------+------------------+
| id |         discipline_name | used_disciplines |
+----+-------------------------+------------------+
| 10 |        Crane Op Level 3 |                1 |
+----+-------------------------+------------------+

Query I tried to use to select all data:

SELECT `disciplines`.*, COUNT(disciplines_certificates.certificate_id) as used_certificates, COUNT(workers_disciplines.discipline_id) as used_disciplines
FROM (`disciplines`)
LEFT JOIN `disciplines_certificates` ON `disciplines_certificates`.`discipline_id` = `disciplines`.`id`
LEFT JOIN `workers_disciplines` ON `workers_disciplines`.`discipline_id` = `disciplines`.`id`
GROUP BY `disciplines`.`id`

Expected Result:

+----+-------------------------+-------------------+------------------+
| id |         discipline_name | used_certificates | used_disciplines |
+----+-------------------------+-------------------+------------------+
| 10 |        Crane Op Level 3 |                 3 |                1 |
| 18 |        Appointed Person |                 2 |                0 |
+----+-------------------------+-------------------+------------------+

Actual Result:

+----+-------------------------+-------------------+------------------+
| id |         discipline_name | used_certificates | used_disciplines |
+----+-------------------------+-------------------+------------------+
| 10 |        Crane Op Level 3 |                 3 |                3 |
| 18 |        Appointed Person |                 2 |                0 |
+----+-------------------------+-------------------+------------------+

You can find the SQLfiddle here: http://sqlfiddle.com/#!9/392c4/3

Table disciplines

+----+-------------------------+
| id |         discipline_name |
+----+-------------------------+
|  1 |              Pipefitter |
|  2 |         Inst Pipefitter |
|  3 |                  Plater |
| 10 |        Crane Op Level 3 |
| 18 |        Appointed Person |
+----+-------------------------+

Table disciplines_certificates

+---------------+----------------+
| discipline_id | certificate_id |
+---------------+----------------+
|            10 |              6 |
|            10 |             15 |
|            10 |             20 |
|            18 |              6 |
|            18 |             15 |
+---------------+----------------+

Table workers_disciplines

+-----------|---------------+
| worker_id | discipline_id |
+-----------|---------------+
|         1 |            10 |
+-----------|---------------+

Thanks.

Jordi Llull · Accepted Answer · 2015-03-09 18:25:31Z

1

Use the DISTINCT statement to count only distinct ids.

    SELECT `disciplines`.*, 
           COUNT(DISTINCT disciplines_certificates.certificate_id) AS used_certificates, 
           COUNT(DISTINCT workers_disciplines.discipline_id) AS used_disciplines
      FROM `disciplines`
 LEFT JOIN `disciplines_certificates` ON `disciplines_certificates`.`discipline_id` = `disciplines`.`id`
 LEFT JOIN `workers_disciplines` ON `workers_disciplines`.`discipline_id` = `disciplines`.`id`
  GROUP BY `disciplines`.`id`

answered Mar 9, 2015 at 18:25

Jordi Llull

8106 silver badges17 bronze badges

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

1 Comment

George Woodward Over a year ago

Didn't realise I was so close! I had to change COUNT(DISTINCT workers_disciplines.discipline_id) to COUNT(DISTINCT workers_disciplines.worker_id) as with more data it wasn't returning the correct used_disciplines.

Collectives™ on Stack Overflow

MySQL COUNT from multiple tables with group

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related