Question 185644 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Group by multiple columns and add them

I have a table something like below

level1 level2 level3 key
A B C k1
A B C K2
A B D k2
A B E k3
A F G k4
A F null k5
A null null k6
A B null k7
A B null k8

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search