php - MYSQLI 查询总和列重复行上的值

Question

添加所有列值 'price' of all duplicate invoice numbers在 ORDERS-TABLE with _flag = y

我可以在 PHP 数组排序或 js 中实现这一点，但理想情况下，如果可能的话，希望在实际的 SQL 查询中实现这一点。

表_订单

_id    |    _invoice_num    |    _name    |    _price    |    _flag

0          123                     bob           200          y
1          123                     bob           300          y
2          555                     mike          100          ...
3          123                     bob           300          y
3          888                     dave          200          y

PHP:

<?php
 // im only after the query as its jsonen_code to ajax --

 $sql = 'select * , select(sum(price)) from table_orders Where _flag ='y' ;

 if ($results=mysqli_query($con,$sql)){

     while ($row=mysqli_fetch_row($results)){

            array_push($thearray,$row);

            } 
  } 

  echo json_encode(array_values($thearray));

 ?>

输出:

 /* output expecting array length 2 rows

 0 , 123 , bob , 800 , y
 1 , 888 , dave, 200 , y

 */

添加:

 <?php

   $the_type = 'the_flag';

   //removes dupes 
   $sql = "SELECT * FROM orders WHERE $the_type = '' GROUP BY _invoice";

  // need to sum the dupes now.

  ?>

Answer 1

这应该有效:

SELECT *,
       SUM(_price) AS total
FROM   _orders
WHERE  _flag = 'y'
GROUP  BY _invoice_num