Attempting to calculate the total sums of price and quantity columns, then display the sum using 'sum_price'. The query functions, but I always end up with $0.00 as sum.

Query

mysqli_query($con,
    "SELECT *, SUM(order_item_price) * SUM(order_item_quantity) AS 'sum_price' 
    FROM order_table, order_item, steam_lessons 
    WHERE steam_lessons.lessonid = order_item.order_plan 
      AND order_table.memberid != order_item.order_idmember 
      AND '".$_SESSION['memberid']."' = order_item.order_idmember 
      GROUP BY order_item.order_item_id 
      ORDER BY order_item.date DESC");

Portion where I read results of query for SUM total:

Sum Price: '.number_format($a3["sum_price"], 2).'

    For a start I think you want SUM(order_item_price * order_item_quantity) as what you have makes no sense. (1+2)×(4+3)≠(1×4)+(2×3).

    Also, even though all columns of all three tables are being selected (the * in the SELECT statement), the only columns with values that make sense will be order_item_id and sum_price and those that are functionally dependent on order_item_id. Columns from order_table or steam_lessons will cause issues because there might be more than one possible value for one order_item_id.

    (A third thing that raises its head: does that formatting mean order_item_price is a floating-point value?)

    And of course it might just be something that happens or doesn't happen in your code between the point you make that call and the point where you use the intuitively-named $a3 array element.

    Write a Reply...