Magento – join 2 collection in custom Grid Magento

The error shows that main_table.volume_id does not exist, because main_table is the alias for the product entity table which does not contain a column volume_id.

You have defined volIds as alias for your volume_product association table. So you need to change it

Change from

$collection->getSelect()->join(array('volumes' => $volsTable),
    'main_table.volume_id = volumes.entity_id', array('volume_name'));

to

$collection->getSelect()->join(array('volumes' => $volsTable),
    'volIds.volume_id = volumes.entity_id', array('volume_name'));

Have a look at the methods Mage_Core_Model_Resource_Db_Collection_Abstract::addExpressionFieldToSelect() for standard models:

/**
 * Add attribute expression (SUM, COUNT, etc)
 * Example: ('sub_total', 'SUM({{attribute}})', 'revenue')
 * Example: ('sub_total', 'SUM({{revenue}})', 'revenue')
 * For some functions like SUM use groupByAttribute.
 *
 * @param string $alias
 * @param string $expression
 * @param array $fields
 * @return Mage_Core_Model_Resource_Db_Collection_Abstract
 */
public function addExpressionFieldToSelect($alias, $expression, $fields)

And Mage_Eav_Model_Entity_Collection_Abstract::addExpressionAttributeToSelect() for EAV models:

/**
 * Add attribute expression (SUM, COUNT, etc)
 *
 * Example: ('sub_total', 'SUM({{attribute}})', 'revenue')
 * Example: ('sub_total', 'SUM({{revenue}})', 'revenue')
 *
 * For some functions like SUM use groupByAttribute.
 *
 * @param string $alias
 * @param string $expression
 * @param string $attribute
 * @return Mage_Eav_Model_Entity_Collection_Abstract
 */
public function addExpressionAttributeToSelect($alias, $expression, $attribute)

Usage

$collection->addExpressionFieldToSelect("page_view", "sum({{page_view}})", ["page_view"]);
//                                      ^ alias      ^ expression          ^ used fields

$collection->getSelect()->group("main_table.entity_id");

or

$collection->addExpressionAttributeToSelect("page_view", "sum({{page_view}})", "page_view");
//                                          ^ alias      ^ expression          ^ used attribute

$collection->getSelect()->group("main_table.entity_id");

Note that the method suggested in the doc block, groupByAttribute() is only used to group by EAV attributes, not static columns like the entity id.