Question 227287 in Mysql Question posted in
The official MySQL documentation can be found here.

How to count referenced items in same table in MySQL?

Updated with new table definition

I may be too tired to understand how to achieve the following. I have a table where items refer to each other within the same table. I would like to count how many references each item has.

To simplify I have defined a table of persons where the fathers are linked from their children. The table is created by following:

DROP TABLE IF EXISTS persons;

CREATE TABLE persons (
  id        INT AUTO_INCREMENT,
  isfather  TINYINT,
  name      VARCHAR(20),
  father_id INT,
  PRIMARY KEY (id));

INSERT INTO persons (name, isfather, father_id) 
       VALUES ('John', 1, 0), ('Mike', 1, 0), ('Anne', 0, 1), 
              ('Peter', 0, 1), ('Jane', 0, 2), ('Olivia', 0, 1), 
              ('Max', 0, 2), ('Eric', 1, 0), ('Hugh', 0, 0);

SELECT * FROM persons;

This outputs the following table:

enter image description here

What I would like to achieve is a query that counts how many children each father has. I.e. John has 3, Mike has 2 and Eric has 0:

enter image description here

I have managed to create similar queries, but with two different tables (e.g. a parents table, and a children table). I used SELECT DISTINCT(...), COUNT(...) FROM ... INNER JOIN. But I cannot understand how to use a query with JOIN or similar on the same table. Note that a child must not have a parent, and a parent doesn’t necessarily has a child. I have struggled so long now and my brain is tired to think clearly. I hope somebody can help 🙂

Tags:

2

Answers


  1. 0

    Use a self-join:

    SELECT father.name, COUNT(child.id) AS children
FROM persons AS father
LEFT JOIN persons AS child ON child.father_id = father.id
GROUP BY father.id

    Using LEFT JOIN will get 0 counts for people without any children. Change to INNER JOIN if you don’t need them.

    Login or Signup to reply.
  2. 0

    I would prefer to write this as a simple correlation:

    select name, (
    select Count(*) from persons p2
    where p2.father_id = p.id
  ) Children
from persons p
where isfather = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search