Mei_R - 1 year ago 95
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`
``````

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)
``````
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download