Answer the question
In order to leave comments, you need to log in
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
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
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
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 questionAsk a Question
731 491 924 answers to any question