Group multidimensional array data based on two column values and sum values of one column in each group

Question

I have an array which is created as a combination of two database queries from two separate databases, it looks similar to:

$arr1 = [
    ['part' => '1', 'address' => 'aaa', 'type' => '1', 'count' => 5],
    ['part' => '1', 'address' => 'bbb', 'type' => '1', 'count' => 5],
    ['part' => '1', 'address' => 'ccc', 'type' => '1', 'count' => 5],
    ['part' => '2', 'address' => 'aaa', 'type' => '1', 'count' => 5],
    ['part' => '2', 'address' => 'bbb', 'type' => '1', 'count' => 5],
    ['part' => '2', 'address' => 'ccc', 'type' => '2', 'count' => 5]
];

I am looking for a way to group this array by part and type values. I also need to know the total of the count values as they are grouped.

The results would be something like:

$arr2 = [
    ['part' => '1', 'type' => '1', 'count' => 15],
    ['part' => '2', 'type' => '1', 'count' => 10],
    ['part' => '2', 'type' => '2', 'count' => 5]
];

but I just can't see how to do this. I have seen a few examples of grouping by a single key/value, but not by multiple values at once.

I thought about that, but the data is generated from two (physically) separate databases, so I would have to use php to get two lots of data, import it back into MySQL, run the query, and export it again, I figured if I could do it in PHP it would be quicker. — IGGt, Jan 25 '13 at 14:44

MarcDefiant · Accepted Answer · 2013-01-25T15:01:06.460

9

This function should do the job.

function groupByPartAndType($input) {
  $output = Array();

  foreach($input as $value) {
    $output_element = &$output[$value['part'] . "_" . $value['type']];
    $output_element['part'] = $value['part'];
    $output_element['type'] = $value['type'];
    !isset($output_element['count']) && $output_element['count'] = 0;
    $output_element['count'] += $value['count'];
  }

  return array_values($output);
}

If both databases are on the same database server you would be able to do this using SQLs GROUP BY feature.

edited Jan 25 '13 at 15:01

answered Jan 25 '13 at 14:46

MarcDefiant

6,649
6
29
49

This is unsafe. You might have part: `part_` and type: `type`. You also may have part: `part` and type: `_type`. Even though we have different pair this code fails to distinct them. – Tom Raganowicz Mar 28 '20 at 21:23

score 1 · Answer 2 · answered Jan 25 '13 at 15:12

The following:

$arr2 = array();
foreach ($arr1 as $a) {

  unset($a['address']);
  $key = $a['type'] . '-' . $a['part'];

  if (isset($arr2[$key])) {
    $arr2[$key]['count'] += $a['count'];
  } else {
    $arr2[$key] = $a;
  }

}
$arr2 = array_values($arr2);

Would output

array
  0 => 
    array
      'part' => string '1' (length=1)
      'type' => string '1' (length=1)
      'count' => int 15
  1 => 
    array
      'part' => string '2' (length=1)
      'type' => string '1' (length=1)
      'count' => int 10
  2 => 
    array
      'part' => string '2' (length=1)
      'type' => string '2' (length=1)
      'count' => int 5

score 0 · Answer 3 · answered Jan 25 '13 at 14:50

0

Something like

 $newarr=array();
 foreach ( $arr as $Key => $Value ) {
 $newarr[$Value[part]][]=$arr[$key];
 }


 foreach ( $newarr[part] as $Key => $Value ) {
 ...
 }

answered Jan 25 '13 at 14:50

el Dude

5,003
5
28
40

Please always post some explanation with your answers on Stackoverflow. Code-only answers do a poor job of educating the OP and countless future researchers. – mickmackusa Aug 25 '18 at 10:55

score 0 · Answer 4 · answered Jul 24 '15 at 14:20

Full answer for multi-keys arrays grouping is

// *    $arr - associative multi keys data array
// *    $group_by_fields - array of fields to group by
// *    $sum_by_fields - array of fields to calculate sum in group

function array_group_by($arr, $group_by_fields = false, $sum_by_fields = false) {
    if ( empty($group_by_fields) ) return; // * nothing to group

    $fld_count = 'grp:count'; // * field for count of grouped records in each record group

    // * format sum by
    if (!empty($sum_by_fields) && !is_array($sum_by_fields)) {
        $sum_by_fields = array($sum_by_fields);
    }

    // * protected  from collecting
    $fields_collected = array();

    // * do
    $out = array();
    foreach($arr as $value) {
        $newval = array();
        $key = '';
        foreach ($group_by_fields as $field) {
            $key .= $value[$field].'_';
            $newval[$field] = $value[$field];
            unset($value[$field]);
        }
        // * format key
        $key = substr($key,0,-1);

        // * count
        if (isset($out[$key])) { // * record already exists
            $out[$key][$fld_count]++;
        } else {
            $out[$key] = $newval;
            $out[$key][$fld_count]=1;
        }
        $newval = $out[$key];

        // * sum by
        if (!empty($sum_by_fields)) {
            foreach ($sum_by_fields as $sum_field) {
                if (!isset($newval[$sum_field])) $newval[$sum_field] = 0;
                $newval[$sum_field] += $value[$sum_field];
                unset($value[$sum_field]);
            }
        }

        // * collect differencies
        if (!empty($value))
            foreach ($value as $field=>$v) if (!is_null($v)) {
                if (!is_array($v)) {
                    $newval[$field][$v] = $v;
                } else $newval[$field][join('_', $v)] = $v; // * array values 
            }

        $out[$key] = $newval;
    }
    return array_values($out);
}

score 0 · Answer 5 · answered Dec 05 '20 at 14:24

If this task was necessary in one of my projects, I would craft a snippet that would not need reference variables or any iterated function calls.

Inside the loop, declare the composite temporary key as a variable (since it is used more than once). Push the new row into the result array using the composite key as the temporary first-level key.

Use the null coalescing operator to use the pre-existing count for a given group or zero if the group has not yet been encountered. Then add the new count value to the previously accumulated count.

This technique will unconditionally overwrite the encountered group every time it is repeated. In doing so, the data will be updated with the correct part, type, and count values throughout the iterative process.

When the loop finishes, re-index the result array by calling array_values().

Code: (Demo)

$arr1 = [
    ['part' => '1', 'address' => 'aaa', 'type' => '1', 'count' => 5],
    ['part' => '1', 'address' => 'bbb', 'type' => '1', 'count' => 5],
    ['part' => '1', 'address' => 'ccc', 'type' => '1', 'count' => 5],
    ['part' => '2', 'address' => 'aaa', 'type' => '1', 'count' => 5],
    ['part' => '2', 'address' => 'bbb', 'type' => '1', 'count' => 5],
    ['part' => '2', 'address' => 'ccc', 'type' => '2', 'count' => 5]
];

$result = [];
foreach ($arr1 as $row) {
    $compositeKey = $row['part'] . '-' . $row['type'];
    $result[$compositeKey] = [
        'part' => $row['part'],
        'type' => $row['type'],
        'count' => ($result[$compositeKey]['count'] ?? 0) + $row['count']
    ];
}
var_export(array_values($result));

Output:

array (
  0 => 
  array (
    'part' => '1',
    'type' => '1',
    'count' => 15,
  ),
  1 => 
  array (
    'part' => '2',
    'type' => '1',
    'count' => 10,
  ),
  2 => 
  array (
    'part' => '2',
    'type' => '2',
    'count' => 5,
  ),
)

p.s. Ideally, this task probably could/should be performed in the sql but we don't have the details to provide any specific guidance.

Group multidimensional array data based on two column values and sum values of one column in each group

5 Answers5

Linked

Related