Using stored function in MySQL workbench to create a new column to show the results of my calculation

user_19563696 0
Viewed 45

I'm trying to do a calculation using a stored function and I'm not sure where I'm going wrong so would really appreciate your help. I'm looking to calculate the average value of three of the columns in my table and then subtract another column from this and input the result in a new column.

I'm new to SQL and am struggling with stored functions so I appreciate I may be making a pretty big error! Also I'm not sure if perhaps I should be using SET instead (or another key word). I've included the error at the bottom.


DELIMITER //
CREATE FUNCTION project_students.Target_compare_calc
(Target_grade INTEGER, Engish_WAG INTEGER, Maths_WAG INTEGER, Science_WAG INTEGER)
RETURNS INTEGER 
DETERMINISTIC

BEGIN 
    DECLARE Target_comp INTEGER;
    SELECT AVG(English_WAG,Maths_WAG,Science_WAG)-Target_grade
    FROM project_students.pupil_attainment

RETURN Target_comp;
END //

DELIMITER;

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ',Maths_WAG,Science_WAG)-Target_grade FROM project_students.pupil_attainment ' at line 8

stored-functions mysql
1 Answers

avg syntax is AVG([DISTINCT] expr) expr is a column over rows, you seem to want an average per student over several columns and one row so avg is not appropriate,you need to do it by hand eg select (a+b+c) / 3 from t; and you need to terminate the statement.

user_61524000
Related