mysql - Calculate sum of absolute difference in rows by groups -

i have problem have been able solve using stata, data has grown size cannot process memory more. hope in mysql instead. trying calculate manhattan distance of items between n groups. have manipulated data far hope ready work computation:

select * exampleshares;  +----------+-------------+-------------+ | item     | group       | share       | +----------+-------------+-------------+ |        | group1      |  .3         | | b        | group1      |  .7         | |        | group2      |  .2         | | b        | group2      |  .6         | | c        | group2      |  .2         | |        | group3      |  .3         | | c        | group3      |  .6         | +----------+-------------+-------------+ 

the manhattan distance of example :

+----------+-------------+-------------+ | groupx   | groupy      | m distance  | +----------+-------------+-------------+ | group1   | group1      | 0           | | group1   | group2      |  .4         | | group1   | group3      | 1.3         | | group2   | group1      |  .4         | | group2   | group2      | 0           | | group2   | group3      | 1.1         | | group3   | group1      | 1.3         | | group3   | group2      | 1.1         | | group3   | group3      | 0           | +----------+-------------+-------------+ 

for example, distance between group1 , group2 computed |.3-.2|+|.7-.6|+|0-.2|=0.4, ie. sum of absolute difference in shares. how do in mysql?

during search if found couple of solutions calculating difference previous row group, nothing looking specifically.

i believe have use stored routine or other script accomplish this. here stored routine it:

delimiter // drop procedure if exists manhattandistance// create procedure manhattandistance (in startgroup char(32), in endgroup char(32), out manhattandistance decimal(2,1))     not deterministic     reads sql data begin   drop table if exists tmp_items;   create temporary table tmp_items select distinct item exampleshares;    select sum(abs(ifnull(es1.share, 0) - ifnull(es2.share, 0))) manhattandistance     tmp_items ti     left join exampleshares es1 on es1.item = ti.item , es1.group = startgroup     left join exampleshares es2 on es2.item = ti.item , es2.group = endgroup; end// delimiter ;  call manhattandistance('group1', 'group2', @distancebetweengroup1and2); select @distancebetweengroup1and2;