Using a mysql user-defined function to calculate dollar amount. Error 1064 (42000)

Question

I'm trying to create a mysql function to calculate the total spent by the top five customers in a store but I keep receiving the following syntax error. What is causing the error? Error:

ERROR 1064 (42000): 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
'FUNCTION costofbestbuyers (totalspent DECIMAL)
RETURNS DOUBLE
BEGIN
    DECLA' at line 1

Function:

DELIMITER // 
CREATE FUNCTION topfivespenders (totalspent DECIMAL) 
RETURNS DOUBLE 
BEGIN 
    DECLARE totalspent DOUBLE DEFAULT 0; 

    SELECT sum(ordercost) AS totalspent
      FROM customer c JOIN orders o 
      ON c.customerID = o.cID 
      GROUP BY o.cID 
      ORDER BY totalspent desc 
      LIMIT 5; 

RETURN totalspent; 
END; //

score 0 · Answer 1 · answered Oct 15 '17 at 16:55

Syntax is incorrect for setting value of sum(ordercost) into totalspent variable.

Try this. Hope this works.

DELIMITER // 
CREATE FUNCTION topfivespenders (totalspent DECIMAL) 
RETURNS DOUBLE 
BEGIN 
    DECLARE totalspent DOUBLE DEFAULT 0; 

    SELECT sum(ordercost) INTO totalspent
      FROM customer c JOIN orders o 
      ON c.customerID = o.cID 
      GROUP BY o.cID 
      ORDER BY totalspent desc 
      LIMIT 5; 

RETURN totalspent; 
END//
DELIMITER //

score 0 · Answer 2 · answered Oct 15 '17 at 17:10

Since you got a syntax error where you did, I would suppose you're trying to execute this in phpMyAdmin or with an API. You don't need to do the DELIMITER trick in those environments. You only need DELIMITER when you're using the mysql client to execute SQL statements or an SQL script. See the answer to this question: Creating functions in phpMyAdmin - Error: access denied you need the super privilege for this operation

Besides that, I see some other problems with your function. You haven't encountered these problems yet if you haven't successfully defined the function.

MySQL stored functions can only return a scalar, not a result set. You're trying to return a result set of up to five groupwise sums.
Your ORDER BY clause is ordering by a local variable totalspent which you give no value except its default value of 0. This means your ORDER BY clause will order by a constant value, which makes every row tied in the sort order. It'll be as if you had no sort order, and MySQL will order the rows in some arbitrary manner.

Using a mysql user-defined function to calculate dollar amount. Error 1064 (42000)

2 Answers2