SP how can I sum the amount of all leftchild in parentid I have this data and table structure,I am creating MySQL stored procedure to sum all the amount in the leftchild of the parentid, I don't have enough knowledge in stored procedure.and I need some help please because I'm lost.

CREATE TABLE IF NOT EXISTS `mytree` (
  `parentid` int(11) NOT NULL,
  `memberid` int(11) NOT NULL,
  `position` char(1) NOT NULL,
  `amount` decimal(10,2) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;


INSERT INTO `mytree` (`parentid`, `memberid`, `position`, `amount`) VALUES
    (8, 27, 'R', 0.00),
    (8, 28, 'L', 0.00),
    (24, 26, 'R', 0.00),
    (0, 1, '', 5500.00),
    (24, 25, 'L', 0.00),
    (21, 24, 'L', 500.00),
    (21, 23, 'R', 0.00),
    (18, 20, 'R', 1500.00),
    (18, 19, 'L', 0.00),
    (15, 18, 'R', 2000.00),
    (15, 17, 'L', 0.00),
    (13, 16, 'L', 0.00),
    (13, 15, 'R', 2500.00),
    (12, 14, 'R', 0.00),
    (12, 13, 'L', 3000.00),
    (10, 12, 'R', 3500.00),
    (10, 11, 'L', 0.00),
    (7, 10, 'R', 4000.00),
    (7, 9, 'L', 0.00),
    (5, 8, 'R', 500.00),
    (5, 7, 'L', 4500.00),
    (1, 6, 'R', 0.00),
    (1, 5, 'L', 5500.00),
    (20, 22, 'R', 0.00),
    (20, 21, 'L', 1000.00);


here is my stored proc

CREATE DEFINER=`root`@`localhost` PROCEDURE `count_left_right_amount`(IN `p_memid` INT, OUT `tot_left_amount` DECIMAL(10,2))
    LANGUAGE SQL
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

DECLARE row_total decimal(10,2);
DECLARE total decimal(10,2);
DECLARE m_left int;
DECLARE run_left int;
DECLARE p_id int;

set total =  0;

select memberid into p_id from mytree where position = 'L' AND parentid = p_memid;




WHILE  p_id != 0 DO

 select sum(amount) into row_total from mytree where parentid = p_id 
  GROUP BY parentid;

 select memberid into m_left from mytree where parentid = p_id;
 set total = total + row_total;
 set tot_left_amount = total;
 set p_id = m_left;


END WHILE;


END


Example if the parentid is 5 ,so the leftchild is 7 then go down to his subtree sum it all,so the total amount in the left child is '22500'? please correct me if I'm wrong.

please help me.

Thank you in advance.
Assuming the following:

alter table mytree add constraint ck1tree unique (memberid);
alter table mytree add constraint ck2tree unique (parentid,position);


One suggestion is to start with a procedure that calculates the sum of the subtree (regardless of position):

There is probably more elegant solutions to this, but I'm in a bit of a hurry and I think it will get you started so I'll post anyhow. Note that you will probably have to adjust the variable @@max_sp_recursion_depth;

set @@max_sp_recursion_depth = 10;
delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE sum_subtree
    (IN `p_memid` INT, OUT `tot_left_amount` DECIMAL(10,2)  
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
    DECLARE tmp decimal(10,2) default 0;
    DECLARE tmp2 decimal(10,2) default 0;
    DECLARE a decimal(10,2);
    DECLARE mid int;
    DECLARE finished int default 0;

    DECLARE c1 CURSOR FOR
        select memberid, amount
        from mytree where parentid = p_memid;

    DECLARE CONTINUE HANDLER
        FOR NOT FOUND SET finished = 1;

    OPEN c1;
    get_amount: LOOP
        FETCH c1 into mid, a;
        IF finished = 1 THEN
            LEAVE get_amount;
        END IF;

        -- recursive call to procedure 
        CALL sum_subtree(mid, tmp);
        SET tmp2 = tmp2 + tmp + a;
    END LOOP;
    SET tot_left_amount = tmp2;
END $$
delimiter ;


call sum_subtree(7,@x);
select @x;
+----------+
| @x       |
+----------+
| 18000.00 |
+----------+


Now you can create a procedure that select the value from the 'L' branch, call this procedure and return the sum of those two values. Hope it helps

Edit: example of calling procedure:

delimiter $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `count_left_right_amount`(
    IN `p_memid` INT, OUT `tot_left_amount` DECIMAL(10,2)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
BEGIN
    DECLARE a decimal(10,2);
    DECLARE m int;
    DECLARE s decimal(10,2);

    select memberid, amount into m, a from mytree
    where parentid = p_memid and position = 'L';

    call sum_subtree(m,s);
    set tot_left_amount = a + s;
END $$
delimiter ;


call count_left_right_amount(5,@y);

MariaDB [test]&gt; select @y;
+----------+
| @y       |
+----------+
| 22500.00 |
+----------+
1 row in set (0.00 sec)