Sum() the output of a function in SQL

Question

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'";

and what was the problem? Other than you reversed the parameters to your function — Garr Godfrey, Sep 15 '21 at 22:19
your php code is **vulnerable** to **sql inection** so use **prepared statements with paramenerts** — nbk, Sep 15 '21 at 22:23
Can you open your MySQL error log file and share the MySQL error from there? — Rajeev Singh, Sep 23 '21 at 04:48

Garr Godfrey · Accepted Answer · 2021-09-15T22:41:15.433

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 ]);

I edited to better answer the question of how to use the function, and to address the updated question regarding for "each customer" — Garr Godfrey, Sep 15 '21 at 22:39

score 0 · Answer 2 · answered Sep 15 '21 at 22:37

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?

Sum() the output of a function in SQL

2 Answers2