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: stackoverflow.com/questions/11899647/… — Filipe Martins
– Filipe Martins, Commented Apr 24, 2017 at 20:52

Juan Carlos Oropeza · Accepted Answer · 2017-04-24 20:50:52Z

0

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

answered Apr 24, 2017 at 20:50

Juan Carlos Oropeza

48.4k14 gold badges87 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

mysql count chid types of each parent

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related