Sum() the output of a function in SQL-CodePudding

I am trying to achieve a sum of all orders of a cuctomer. I have created an SQL function to calculate the amount of a given order:

DELIMITER $$
CREATE FUNCTION invoices.orderAmount(quantity INT, price INT) RETURNS INT
BEGIN
  DECLARE orderTotal INT;
  SET orderTotal = quantity * price;
  RETURN orderTotal;
END$$
DELIMITER ;

while trying to save this function, i get this error: now I want to get all the sums of all customer's orders with a command like:

$totalOrderQuery =" SELECT price, quantity, sum(invoices.orderAmount(price,quantity)) AS CustomersTotalBill FROM invoices WHERE purchaseId=$purchaseId AND status='ordered' AND OrderCancel='NO'";

CodePudding user response：

The use of a stored function seems overkill for such a simple operation.
You reversed the parameters in the call to your function
You are missing a closing parenthesis in your SELECT statement
The price and quantity columns in your SELECT are meaningless because the SUM will cause the results to be grouped.
You didn't specify a "GROUP BY" so all results will be in one row. Use GROUP BY to group by customer id

You should be able to do something like this:

SELECT SUM(invoices.orderAmount(quantity,price)) AS CustomersTotalBill FROM invoices WHERE  purchaseId=:PurchaseID AND status='ordered' AND OrderCancel='NO'
GROUP BY invoices.customerid

(assuming you have a customerid column to identify the customer)

EDIT: original suggestion removed the function call and didn't use GROUP BY:

SELECT SUM(price * quantity) AS CustomersTotalBill FROM invoices WHERE  purchaseId=:PurchaseID AND status='ordered' AND OrderCancel='NO'

Use a prepared statement to provide the value to PurchaseID. If using PDO:

  $stmt = $pdo->prepare($QUERY);

 $stmt->execute([ 'PurchaseID' => $purchaseId ]);

CodePudding user response：

Your error message tells you what is wrong

change the Function to

DELIMITER $$
CREATE FUNCTION invoices.orderAmount(quantity INT, price INT) RETURNS INT
DETERMINISTIC
BEGIN
  DECLARE orderTotal INT;
  SET orderTotal = quantity * price;
  RETURN orderTotal;
END$$
DELIMITER ;

But use only prepared statements with parameters in your php code. see How can I prevent SQL injection in PHP?