V
V
Vladislav2020-06-20 15:44:40
MySQL
Vladislav, 2020-06-20 15:44:40

How to count for each row of a table the number of occurrences of this row in another table?

There are two tables course and history
In the table course: id and name
In the table history: id, course_id, status It is
necessary for each line of course to count the number of lines in history
The output should be

Result
5eee03f54df62769124518.png

Now I have this option:
SELECT c.`name` AS course_name, COUNT(h.id) AS `quantity`
FROM `course` c
JOIN `history` h ON(h.course_id = c.course_id)
WHERE h.`status` = 1
GROUP BY c.`course_id`

Answer the question

In order to leave comments, you need to log in

2 answer(s)
K
Konstantin Tsvetkov, 2020-06-20
@jakblack377

SELECT name AS course_name, COUNT(history.id) AS quantity
  FROM course
    LEFT JOIN history ON course_id = course.id AND status = 1
  GROUP BY name

V
Vladislav, 2020-06-21
@jakblack377

If the task was to count the number of entries in History for each record in the Course table, and zeros should also have been present in the response from the base, then the query should look like this:

select c.`name` AS course_name,  
       (select COUNT(*) from history where history.`course_id` = c.`course_id` AND history.`status` = 1) as quantity
from course c

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question