mysql count chid types of each parent

Question

I have a parent table like this:

id | name
-------------
1  | item a
2  | item b

and have a chid table with a column with 2 posible types

parent id | name       | type
-------------------------------
1         | subitem a  | t1
1         | subitem b  | t1
2         | subitem c  | t2
2         | subitem d  | t1
2         | subitem e  | t2

there is a way to make a select with the following result?

parent id |  childs type t1 | childs type t2
-----------------------------------------------
1         |      2          |      0
2         |      1          |      2

any ideas? Thanks!!

Maybe your answer is here: http://stackoverflow.com/questions/11899647/select-dynamic-columns-in-mysql — Filipe Martins, Apr 24 '17 at 20:52

score 0 · Answer 1 · answered Apr 24 '17 at 20:50

SELECT c.parent_id,
       COUNT( case when c.type = 't1' then 1 end) as  `childs type t1` ,
       COUNT( case when c.type = 't2' then 1 end) as  `childs type t2`
FROM child c
GROUP BY c.parent_id

To include parents without children

SELECT p.id as parent_id,
       COUNT( case when c.type = 't1' then 1 end) as  `childs type t1` ,
       COUNT( case when c.type = 't2' then 1 end) as  `childs type t2`
FROM parent p
LEFT JOIN child c
       on p.id = c.parent_id
GROUP BY p.id

mysql count chid types of each parent

1 Answers1