CODEWITHSUNDEEP
mysql
Amelia
Amelia

Reputation: 320

creating mysql function using phpmyadmin

I want to enter the following function to count a number of finished tasks from a mysql table in phpmyadmin, but its always returning a none descriptive error:

 DELIMITER $$
 CREATE FUNCTION `num_completed`(v1 INT)
 RETURNS INT
 BEGIN
 DECLARE icm INT;
 SELECT SUM(IF(completed='yes',1,0)) AS completed INTO icm FROM ri_t_course_progress WHERE enrollment_id=v1;
 RETURN icm;
 END$$

The query itself should be correct. I've tested it and returns the desired result. Anybody know whats wrong?

Upvotes: 0

Views: 3560

Answers (1)

Joni
Joni

Reputation: 111219

You need to assign the result of the SELECT into the variable. Here's one way:

 SELECT SUM(IF(completed='yes',1,0)) INTO icm 
 FROM ri_t_view_course_progress WHERE enrollment_id=v1;

Upvotes: 2

Related Questions