how to sum column value using group by collection in magento(USING MAGENTO STANDARD)

how to sum column value using group by collection in magento(USING MAGENTO STANDARD)

September 21, 2026 · Illustrated author avatarBy Stefan Bogdanescu
how to sum column value using group by collection in magento(USING MAGENTO STANDARD)

Summing Column Values Using Group By Collection in Magento

As a senior Magento developer, I often find myself needing to perform complex operations on collections pulled from my database. Recently, one of our clients asked how to sum certain columns while grouping by another column using only the standard Magento framework. In this post, we’ll walk through the process step-by-step.

Understanding the Challenge

Our table test/p1 has a structure like this:

idcat_idratep_id
1141
2121
3132
4253
5231

We have a collection $testmodel = Mage::getModel('test/p1')->getCollection();. Our goal is to get an output like this:

cat_idratecount_category
193
282

Solving the Problem

To achieve this, we’ll need to use Magento’s built-in collection functionality in combination with some PHP magic. Here’s how:

Step 1: Group By Category ID

First, let’s group our collection by cat_id. We can do this using the groupByKey method:

$testmodel->groupByKey('cat_id');

This will group all items in the collection based on their cat_id.

Step 2: Modify Each Group Item

Next, we need to modify each group item. We’ll create a new array with the desired structure and populate it using a loop:

$newCollection = [];
foreach ($testmodel as $item) {
    $groupedData[$item->getId()] = [
        'cat_id' => $item->getCatId(),
        'rate'   => 0,
        'count_category' => 0,
    ];
}

Step 3: Sum the Rates and Count Categories

Now, let’s loop through each item in our original collection again. We’ll find its group in $groupedData (using the cat_id) and update the rate and count_category values:

foreach ($testmodel as $item) {
    if (!isset($groupedData[$item->getCatId()])) continue;

    // Sum rates
    $groupedData[$item->getCatId()]['rate'] += $item->getRate();

    // Count categories
    $groupedData[$item->getCatId()]['count_category']++;
}

Step 4: Final Output

Finally, we can convert our $groupedData array back into a Magento collection:

$newCollection = new Varien_Data_Collection();
foreach ($groupedData as $item) {
    $obj = Mage::getModel('test/p1');
    foreach ($item as $key => $value) {
        $obj->setData($key, $value);
    }
    $newCollection->addItem($obj);
}

Conclusion

As you can see, Magento’s standard collection functionality provides a powerful way to manipulate data at the application level. By using groupByKey, looping through our collections, and creating new objects, we were able to achieve the desired output.

Remember, when working with Magento, it’s important to understand both the database structure and the available PHP functions in the core library. This knowledge allows you to write efficient code that leverages Magento’s built-in features while avoiding unnecessary queries or manipulations.

For more advanced collection manipulation techniques, I recommend checking out our guide on Working with Collections in Magento.

Happy coding!

Catalog Database Errors CLI

Our network