MYSQLI Query sum column duplicates value on rows

Question

Add all column value 'price' of all duplicate invoice numbers on ORDERS-TABLE with _flag = y

I can achieve this in PHP array sorting or js but ideally, want this in the actual SQL query if possible.

Table_ORDERS

_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:

 /* output expecting array length 2 rows

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

 */

added:

 <?php

   $the_type = 'the_flag';

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

  // need to sum the dupes now.

  ?>

no i need the complete rows still but just adding the duplicate values together and displaying the duplicate row once as the OUTPUT: — joshua
– joshua, Commented May 17, 2018 at 2:45
shouldn't group by do what you need? It will still display all other records but combine duplicates. — hungrykoala
– hungrykoala, Commented May 17, 2018 at 2:47
The above is an example simplified but that not how i am implementing the actual issue. essentially i want to display all rows but only one of a single duplicate and add all them duplicates together on its price column — joshua
– joshua, Commented May 17, 2018 at 2:51
group by ( removes dupes) then i want to sum the dupes but still display atleast 1 record of it then the rest as normal.. Basically every item has an invoice number so if you buy 2 phones they share the same invoice number because there on the same invoice but when i display them or search i dont want to show each row rather just show all invoice numbers and totals of cost as each row as long as the flag = 'y' — joshua
– joshua, Commented May 17, 2018 at 3:04

supravi · Accepted Answer · 2018-05-17 05:45:47Z

1

This should work:

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

answered May 17, 2018 at 5:45

supravi

595 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

joshua Over a year ago

I was missing the comma after * symbol.. This works, but i will say it adds an extra column at end of the row results with as total. This does solve my problem just not how i expected it. Thank you

Collectives™ on Stack Overflow

MYSQLI Query sum column duplicates value on rows

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related