Mei_R Mei_R - 9 days ago 5
MySQL Question

count total even and odd number between 20-50 with mysqll procedure

i'm having hard time with my homework , because im beginner using mysql procedure.
to search total numbers odd and even between 20-50 with looping.
i want to make output like this using mysql procedure to call:
total_odd_numbers :15
total_even_numbers:16

i tried for sum up for even numbers like this :

`DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `genap`()
BEGIN
DECLARE x INT;
DECLARE str VARCHAR(255);
declare total int;

SET x = 20;
SET str = '';
set total = 0;

loop_label: LOOP
IF x = 50 THEN
LEAVE loop_label;
END IF;
SET x =x+1;

IF (x mod 2) THEN
ITERATE loop_label;


ELSE
SET str = CONCAT(str,x,',');
SET total= total+x;
END IF;
END LOOP;
SELECT sum(total);

END`

Answer

Try this procedure:

DROP PROCEDURE IF EXISTS ShowOddEvesBetween;
DELIMITER ;;
CREATE PROCEDURE ShowOddEvesBetween(IN fromNum INT, IN toNum INT)
 BEGIN
 DECLARE odds INT DEFAULT 0;
 DECLARE evens INT DEFAULT 0;
 DECLARE x INT;

 SET x = fromNum;

 WHILE x <= toNum DO
  IF x % 2 THEN
   SET odds = odds + 1;
  ELSE
   SET evens = evens + 1;
  END IF;

  SET x = x + 1;
 END WHILE;

 SELECT CONCAT("total_odd_numbers: ", odds, ", total_even_numbers: ", evens) AS "Odds & Evens";

 END;
;;

Then call it

CALL ShowOddEvesBetween(20, 50);

Will output what you want

MariaDB [test]> CALL ShowOddEvesBetween(20, 50);
+-----------------------------------------------+
| Odds & Evens                                  |
+-----------------------------------------------+
| total_odd_numbers: 15, total_even_numbers: 16 |
+-----------------------------------------------+
1 row in set (0.00 sec)
Comments