CODEWITHSUNDEEP
mysqlsqlstored-procedures
compguy24
compguy24

Reputation: 957

stored procedure for calculating a sum - MySQL 5

getting a 'invalid use of group function'

just trying to sum the values of all the column values for 'weight' in a table:

BEGIN

DECLARE v_finished int DEFAULT 0;
DECLARE v_weight int DEFAULT 0;
DECLARE totalWeight int DEFAULT 0;

DECLARE weight_cursor CURSOR FOR 
SELECT weight FROM users_teachers;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
OPEN weight_cursor;

get_weight: LOOP
FETCH weight_cursor into v_weight;
IF v_finished = 1 THEN 
        LEAVE get_weight;
    END IF;
SET totalWeight = totalWeight + v_weight;



END LOOP get_weight;

CLOSE weight_cursor;

END

UPDATE: the problem now is that v_weight is always = 0. that is, it is not returning the values from the DB table, some of which are non-zero

Upvotes: 0

Views: 6089

Answers (1)

Guneli
Guneli

Reputation: 1731

So, as the first error was in the SUM function. Please, try this:

BEGIN

DECLARE v_finished INTEGER DEFAULT 0;
DECLARE v_weight INTEGER DEFAULT 0;
DECLARE totalWeight INTEGER DEFAULT 0;

DECLARE weight_cursor CURSOR FOR 
   SELECT weight FROM users_teachers;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = 1;
OPEN weight_cursor;

get_weight: 
LOOP
FETCH weight_cursor into v_weight;
IF v_finished = 1 THEN 
        LEAVE get_weight;
END IF;
SET totalWeight = totalWeight + v_weight; 

END LOOP get_weight;

CLOSE weight_cursor;

END

Upvotes: 1

Related Questions