Check and count if a column value is used in another column of same table I have a table like this



and looking for a result like this, where the last modified date of a particular id is shown that too if the check is 1. Here id 6 is not in the result because the check is 0, also the 11/4/19 entry of 1 is ignored as the check is 0, so 9/4/19 entry is shown in result.

Next is the children count. it counts the number of times,the parent id is repeated. I really don't want the number of  times,the parent id is repeated,but just want to know if its has child or not.



I am confused how to get the children count. Remaining i did like this

SELECT id, modified,checks 
FROM ( select * 
       from test 
       order by modified desc) as t1 
where checks!=0  group by (id) 


and i got this result



How to get children count too or just if the id got a child?
How to get a result like this
SELECT t1.id, 
       MAX(CASE WHEN t1.`check` THEN t1.modified_date END) modified_date,
       COUNT(DISTINCT t2.id) children_count,
       1 `check`
FROM test t1
LEFT JOIN test t2 ON t1.id = t2.parent_id
GROUP BY t1.id
HAVING modified_date IS NOT NULL;


fidddle