MySQL: Counting Number of Records or Rows by a Foreign Column (from Another Table)

Well, MySQL doesn’t actually have anything called Foreign Key, it’s just my way of saying a alien column from another table.

Let’s say 2 tables are related, 1 is books and the other is subjects. Any book in book table falls into one of the subjects, thus having a subject column referencing the ID of one of the subjects in subject table.

You need to know the number of books from each of the subjects, namely, 243 books in subject Education, 77 books in Science, etc.

Now how do we do this? Simple. Just perform the following SQL query:

SELECT subject.id, COUNT(book.id) FROM subject, book GROUP BY book.subject HAVING book.subject = subject.id

1 thought on “MySQL: Counting Number of Records or Rows by a Foreign Column (from Another Table)”

  1. Pingback: MySQL: Count the Number of Records and Update the Stats Field in Another Table

Comments are closed.

Scroll to Top