How to use group by in magento collection with join query

Question

In module's admin grid I am using this code to get collection and group them by customer id

$collection = Mage::getModel('referafriend/statistics')->getCollection();
$collection->getSelect()->group('entity_id');
$this->setCollection($collection);

but here i have to use renderer and filter functions for customer info like name and email against each entity_id. i want to join customer model with my module's table. for this i have written this code

 $collection = Mage::getModel('customer/customer')->getCollection()
 ->addNameToSelect();
$collection->getSelect()->join(array('refer' => 'table_name'),'refer.entity_id = e.entity_id'
 ); 
 $collection->getSelect()->group('entity_id'); 
 $collection->addAttributeToSelect('*');

but it gives me this error

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'entity_id' in group statement is ambiguous

any help will be highly appreciated.

• Amit Bera: it should be ->group('e.entity_id')
• Jonathan Hussey: You should add this as an answer with some detail on why you need the e.
• Haris: Sorry for this silly mistake. @AmitBera thanks for your help and please add this as answer so that question could be closed.

Answer 1

You need to add table name in group by condition.As you did not added on conditions table name at table group('entity_id') so query did not find columns name

 getSelect()->group('e.entity_id');

Logic is:

$collection->getSelect()->group('TABLE_NAME.FIELD_NAME')

• GregC: In addtion, if you need to group by multiple fields, the just add more -> group() clauses ->group('field1') ->group('field2')
• Dhaduk Mitesh: I want order unique products using group by. I have 2 order with 2 same order item. Currently, it is displayed 4 rows in the grid. But I need 2-row using group by.
• Amit Bera: Share your code
• Dhaduk Mitesh: $collection = $object_manager->create('\Magento\Sales\Model\Order\Item')->getCollection(); $collection->getSelect()->join( ['order' => $this->getTable('sales_order')], 'order.entity_id = main_table.order_id and (if(main_table.parent_item_id IS NULL,main_table.price != 0.0000,main_table.parent_item_id IS NULL))', [ 'order_number' => 'order.increment_id', 'order_store_id' => 'order.store_id', ] );
• Dhaduk Mitesh: I want to group by parent_item_id order wise.