Group by multiple columns and add them

I have a table something like below

I am expecting to carry hierarchy information to child count like below

A-->1
A-->B -->2+1(a)=3
A-->B--->C-->1+2+2( 2 entries for ABC)=5
A--B-->D-->1+2+1=4
A--B-->E-->1-->1+2+1=4
A--F-->1+1=2
A--F-->G-->1+1+1=3

My tried the query below does not give the right result

select CONCAT(ifnull(level1, ''), ' > ', ifnull(level2, ''), ' > ', ifnull(level3, '')) as 'foo',COUNT(*) as 'count' from table t t.level1, t.level2, t.level3; 

How can I apply the aggregate function and fetch the right result.

This query gave me desired result:

with g as (
  select level1 l1, level2 l2, level3 l3, count(1) cnt 
  from mytable group by level1, level2, level3)
select concat(g3.l1, 
              case when g3.l2 is null then '' else concat('>', g3.l2) end, 
              case when g3.l3 is null then '' else concat('>', g3.l3) end) path,
          case when g3.l3 is null then g3.cnt
               else g1.cnt +g2.cnt + g3.cnt end cnt
from g g3
left join g g1 on g1.l2 is null and g3.l3 is not null and g3.l1 = g1.l1
left join g g2 on g2.l2 is not null and g2.l3 is null and g3.l3 is not null 
      and g3.l1 = g2.l1 and g3.l2 = g2.l2 

dbfiddle demo

except for AFG which should be 3? (A once + AF once + AFG once).

Query groups values at first, then self joins all occurences g3 with two occurences g2 and one occurence g1 and presents as requested.

Try the following:

with concat_levels as 
(
  select concat_ws('>', level1, level2) lvl_1_2, 
         concat_ws('>', level1, level2, level3) lvl_1_2_3, count(*) cn
  from table_name
  group by 1, 2
)
select t1.lvl_1_2_3 as levels, 
       t1.cn + coalesce(sum(t2.cn), 0) as res
from concat_levels t1 left join concat_levels t2
on t1.lvl_1_2_3 like (concat(t2.lvl_1_2, '%')) and 
   length(t1.lvl_1_2_3) > length(t2.lvl_1_2_3)
group by t1.lvl_1_2_3, t1.cn
order by t1.lvl_1_2_3

demo