MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
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);
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;


id
name
type_2_count


1
foo
1

2
bar
2

3
baz
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;


id
name
type_2_count


1
foo
1

2
bar
2

3
baz
0