About MySQL Subsum

Question

I am having trouble getting MySQL subtotals.

source data

url count

/test/a/1/temp.txt 10
/test/a/2/temp.txt 10
/test/a/3/temp.txt 10
/test/b/1/temp.txt 10
/test/1/temp.txt 10
/test/1/temp2.txt 10
/test/1/temp3.txt 10

result

url count level

/test    70    1
/test/a    30    2
/test/a/1    10    3
/test/a/1/temp.txt    10    4
/test/a/2    10    3
/test/a/2/temp.txt    10    4
/test/a/3    10    3
/test/a/3/temp.txt    10    4
/test/b    40    2
/test/b/1    10    3
/test/b/1/temp.txt    10    4
/test/1    30    2
/test/1/temp.txt    10    3
/test/1/temp2.txt    10    3
/test/1/temp3.txt    10    3

I want to know how.

Thanks Best Regards.

Answer 1

For your particular data, you can do:

select url, sum(count(*)
from ((select substring_index(url, '/', 2) as url, count
       from source
      ) union all
      (select substring_index(url, '/', 3) as url, count
       from source
      ) union all
      (select substring_index(url, '/', 4) as url, count
       from source
      ) union all
      (select substring_index(url, '/', 5) as url, count
       from source
      ) 
     ) s
group by url
order by url;