FUNCTION "SUM does not exist"

Question

I am using mysql 5.5.11, when i execute the script below

INSERT INTO payments(created,  Amount,  user,  Remarks,  orderid,  paymethod) 
VALUES('2016-09-03', 0.0, 'admin', '', 4, 'Cash');

I get error

SQL Error: FUNCTION mydb.SUM does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual

This is the table schema

CREATE TABLE payments (
  ID         int AUTO_INCREMENT NOT NULL,
  OrderID    int,
  Amount     decimal(11,2),
  Created    varchar(20),
  Remarks    varchar(160),
  user       varchar(60),
  PayMethod  varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci,
  /* Keys */
  PRIMARY KEY (ID)
) ENGINE = InnoDB;

What could be the cause of the error

This is the trigger attached to the table

BEGIN
  /* Trigger text */
  UPDATE Orders Set Paid =(Select SUM (Amount) From Payments AS p Where p.OrderID  = Orders.ID),PayMethod =new.PayMethod  WHere Orders.id = new.OrderID;
   UPDATE Orders Set Bal = Total - Paid  WHere Orders.id = new.OrderID;
END

Answer 1

Look at trigger:

UPDATE Orders Set Paid =(Select SUM (Amount) From Payments AS p Where p.OrderID  = Orders.ID),PayMethod =new.PayMethod  WHere Orders.id = new.OrderID;

MySQL does not accept spaces between function name and parenthesis (unless you have set SQL_MODE=IGNORE_SPACE but that gives you other undesirable side effects)

You can check the SQL_MODE setting by running:

SHOW GLOBAL VARIABLES LIKE 'SQL_MODE';

MySQL documentation about this: link

Some example:

mysql> select sum(5);
+--------+
| sum(5) |
+--------+
|      5 |
+--------+
1 row in set (0.00 sec)

mysql> select sum (5);
ERROR 1305 (42000): FUNCTION sum does not exist

Solution: Remove space

UPDATE Orders Set Paid =(Select SUM(Amount) From Payments AS p Where p.OrderID  = Orders.ID),PayMethod =new.PayMethod  WHere Orders.id = new.OrderID;

Answer 2

Remove spaces between SUM and ( like this: Select SUM(Amount) From Payments.