find sum of duplicate values?

Question

I want to find duplicate ISBN as Distinct and their total quantity as sum. enter image description here

i want ISBN = 9789 total quantity as 509 and ISBN = 978944 total quantity as 194

i am using code as given below

`array('fields'=> array('ERPBookdetail.id','ERPBookdetail.item','ERPBookdetail.book_id','ERPBookdetail.isbn','ERPBookdetail.title',
                                'ERPBookdetail.class','ERPBookdetail.subject','ERPBookdetail.medium','ERPBookdetail.board','ERPBookdetail.edition'
                                ,'ERPBookdetail.db_type','ERPBookdetail.price','ERPOrderDetail.item','ERPOrder.order_id','ERPBookdetail.qty','SUM(ERPBookdetail.qty) as total_quantity'),
                'group' => array('ERPBookdetail.isbn'),
               'order'=>'total_quantity DESC',
              'joins' => array(
                        array(
                    'table' => 'orders',
                    'alias' => 'ERPOrder',
                    'type' => 'INNER',
                        'conditions' => array('OR' => array(
                                                        array('ERPOrder.confirmation' => 1),array('ERPOrder.confirmation' => 'Yes'),
                                    )),
                            ),
        array('table' => 'order_details',
        'alias' => 'ERPOrderDetail',
        'type' => 'INNER',
        'conditions' =>array('ERPOrderDetail.item = ERPBookdetail.item','ERPOrder.order_id = ERPOrderDetail.order_id'),)),)
);`

The query you need is: `SELECT isbn, SUM(qty) FROM mytable GROUP BY isbn` I don't know how to do it in cakePHP unfortunately. — Daan, May 28 '15 at 11:26
I dont know how to do it in sql but to do it in php check out this question http://stackoverflow.com/questions/24150063/in-php-merge-duplicate-set-of-elements-of-an-multidimensional-array-and-sum-the — Szenis, May 28 '15 at 11:28
May be this one would help.. http://stackoverflow.com/questions/4971148/sum-function-in-cakephp-query — Ataboy Josef, May 28 '15 at 13:48

score 0 · Answer 1 · answered May 28 '15 at 11:41

you can do this in mysql by simply

    select isbn, sum(qty) as qty from table group by isbn

if you dont want to change mysql query then in php you can it by this way

   for($isbn_rec = 0 ; $isbn_rec < count($your_outputarray_from_query); $isbn_rec++)
   {
      $new_array[$your_outputarray_from_query['isbn']]['qty'] += $your_outputarray_from_query['qty'];
      // if any isbn records comes again then it will be sum up with older isbn number
   }

and you will get final array as below

  array('9789'=>    array('qty'=>'509')
'978944'=> array('qty'->'194')
  )

find sum of duplicate values?

1 Answers1