Perform calculating on each result of a MySQL search

Question

To calculate a total amount for an order, I go to the articles table, and calculate the sum of the articles for this order.

Here is my code:

SELECT orders.*,
  ROUND(SUM(`orders_article_updated_quantity` * (`orders_article_price` * (100 - orders_article_rate)/100)), 2) AS 'order_Total'
FROM orders JOIN orders_articles
  ON orders.order_id = orders_articles.orders_article_id_order
WHERE oreder_id = '" . $order['order_id'] . "'

This code is working for an order, and I got a good result.

Now I want to do this for a list of orders, or all orders. So I deleted the "WHERE" from my request. And I get only one result, with the sum of all orders.

For each result (order), I want the sum of its own articles.

Unrelated to the need to use GROUP BY, but please learn to use query parameters. Stop interpolating PHP variables into your SQL strings. You are at risk of SQL injection. See https://stackoverflow.com/q/60174/20860 for examples. — Bill Karwin, Aug 23 '20 at 14:29
Could you fix the title? You have a few typos and I don't know what you mean by "mysql seach". Did you mean "on each result of a Mysql search"? — Funk Forty Niner, Aug 23 '20 at 14:30
First i use PHP variable for demo purpose. Second, $order is a private variable, not something user can modify, so how can I risk SQL injection ? Thank you Funk Forty Niner, title is fixed, I missed the typo — betsou, Aug 23 '20 at 14:40
@betsou Ok thanks. Funny I didn't get a notification for it being edited, only for your comment responding to mine above. [I posted on meta about this](https://meta.stackoverflow.com/q/400591/1415724). Hm... odd. — Funk Forty Niner, Aug 23 '20 at 14:44
Please in code questions give a [mre]--cut & paste & runnable code, including smallest representative example input as code; desired & actual output (including verbatim error messages); tags & versions; clear specification & explanation. Give the least code you can that is code that you show is OK extended by code that you show is not OK. (Debugging fundamental.) For SQL that includes DBMS & DDL (including constraints & indexes) & input as code formatted as a table. [ask] Pause work on the overall goal, chop code to the 1st expression not giving what you expect & say what you expect & why. — philipxy, Sep 01 '20 at 03:31

score 3 · Accepted Answer · answered Aug 23 '20 at 14:27

You need aggregation for this. Consider adding a group by clause to the query, like so:

SELECT 
    o.*, 
    ROUND(SUM(orders_article_updated_quantity * orders_article_price * (1 - orders_article_rate/100)), 2) AS order_Total
FROM orders o
INNER JOIN orders_articles oa ON ao.orders_article_id_order = o.order_id
GROUP BY o.order_id

Note that you should prefix each column in the query with the table they belong to - that's not the case for the column within the SUM(), which creates ambiguity.

Another option is a correlated subquery (this allows orders without any article):

SELECT 
    o.*, 
    (
        SELECT ROUND(SUM(orders_article_updated_quantity * orders_article_price * (1 - orders_article_rate/100)), 2) 
        FROM orders_articles oa 
        WHERE ao.orders_article_id_order = o.order_id
    ) AS order_Total
FROM orders o

Hello @GMB ! Thank you for your answer, it worked perfectly (I used the second option). Now I have another problem, I have more than 3000 orders and the query is taking long (about 5 sec). I'm trying to limit the result, how can I do it on this kind of query to have better performance ? I think the best way is to use limit before performing the nested select, but I dont know how to do this in this query) Thank you — betsou, Mar 18 '21 at 11:54

Perform calculating on each result of a MySQL search

1 Answers1