[SalesForce] group by soql query

I have records with same values in custom field, Unique_Name__c. On a Visualforce Page, I have to display records grouped by Unique_Name__c.

Unique_Name__c is custom field on OpportunityLineItem populated using a Workflow Rule from Product2 object (product name).

So I have a query

SELECT Id, Unique_Name__c
FROM OpportunityLineItem
GROUP BY Unique_Name__c

When I run this, I get an error saying:

MALFORMED_QUERY: Field must be grouped or aggregated: Id

When I change query to

SELECT Id, Unique_Name__c
FROM OpportunityLineItem
GROUP BY Id

I get the following error:

MALFORMED_QUERY: Field must be grouped or aggregated: Unique_name__c

Can anyone please help me figuring this out?

Best Answer

There is an inherent irony in grouping by a field that claims in its API Name to be unique. Each grouping should theoretically contain just one record. It would perhaps make more sense if the field were simply renamed Product_Name__c. But I digress. If you are sure you want to do groupings, you can get the record count for each Unique_Name__c as follows:

SELECT count(Id) records, Unique_Name__c
FROM OpportunityLineItem
GROUP BY Unique_Name__c

Any fields you want to query for individually must be grouped. You can group multiple fields, which would look like:

SELECT Id, Unique_Name__c
FROM OpportunityLineItem
GROUP BY Id, Unique_Name__c

Or you can group just one:

SELECT Unique_Name__c
FROM OpportunityLineItem
GROUP BY Unique_Name__c