Select number of child record with condition for each parent record

May 31, 2022

This is what I have:

Table: parent

| id | name |
| -- | ---- |
|  1 | foo  |
|  2 | bar  |
|  3 | baz  |

Table: child

| id | parent_id | type_id |
| -- | --------- | ------- |
|  1 |         2 |       2 |
|  2 |         2 |       2 |
|  3 |      NULL |       2 |
|  4 |         1 |       1 |
|  5 |      NULL |       2 |
|  6 |      NULL |       1 |
|  7 |         1 |       2 |
|  8 |         3 |       1 |

I want to select all the parent records, together with the number of child having type 2 for each parent record:

| id | name | type_2_count |
| -- | ---- | ------------ |
|  1 | foo  |            1 |
|  2 | bar  |            2 |
|  3 | baz  |            0 |

I tried this:

SELECT p.id, name, COUNT(c.id) type_2_count
FROM parent p LEFT JOIN child c ON c.parent_id = p.id
WHERE c.type_id = 2
GROUP BY p.id;

| id | name | type_2_count |
| -- | ---- | ------------ |
|  2 | bar  |            2 |
|  1 | foo  |            1 |

But it’s missing the third record.

And this:

SELECT p.id, name, t.cnt type_2_count
FROM parent p LEFT JOIN (
  SELECT parent_id, COUNT(*) as cnt
  FROM child
  WHERE type_id = 2
  GROUP BY parent_id
) t ON t.parent_id = p.id;

| id | name | type_2_count |
| -- | ---- | ------------ |
|  1 | foo  |            1 |
|  2 | bar  |            2 |
|  3 | baz  |         NULL |

But type_2_count is NULL instead of 0 for the third record.

This is the schema I used:

CREATE TABLE IF NOT EXISTS parent (
  id INT AUTO_INCREMENT,
  name VARCHAR(45) NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO parent VALUES (1, 'foo'), (2, 'bar'), (3, 'baz');

CREATE TABLE IF NOT EXISTS child (
  id INT AUTO_INCREMENT,
  parent_id INT REFERENCES parent(id),
  type_id TINYINT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB;

INSERT INTO child VALUES (1, 2, 2), (2, 2, 2), (3, NULL, 2), (4, 1, 1), (5, NULL, 2), (6, NULL, 1), (7, 1, 2), (8, 3, 1);

>Solution :

In your 1st query the only change you need is to move the condition from the WHERE clause to the ON clause:

SELECT p.id, name, COUNT(c.id) type_2_count
FROM parent p LEFT JOIN child c 
ON c.parent_id = p.id AND c.type_id = 2
GROUP BY p.id;

and in your 2nd query use COALESCE() to turn NULL to 0:

SELECT p.id, name, 
       COALESCE(t.cnt, 0) type_2_count
FROM parent p LEFT JOIN (
  SELECT parent_id, COUNT(*) as cnt
  FROM child
  WHERE type_id = 2
  GROUP BY parent_id
) t ON t.parent_id = p.id;

See the demo.