I'm trying to get the following lines to function however receive a return of

MALFORMED_QUERY: Ordered field must be grouped or aggregated: Line_Number__c.

What is wrong here?

SELECT PricebookEntry.Name, Quantity, UnitPrice, TotalPrice,Product_Line_Description__c, Item_Type_Dependent__c, Line_Number__c 
FROM OpportunityLineItem 
WHERE Item_Type__c = 'SCS' AND Product_or_Labor__c = 'Product' AND OpportunityId = '0060z00002xZhV' 
GROUP BY Item_Type_Dependent__c
ORDER BY Line_Number__c ASC

When you're querying for an aggregate result, each row in the result is a combination of rows that have the same value for fields in the group by expression. So, the aggregate of multiple records does not have one Line_Item_Number__c value to return. Each record has one, but the grouping of those records does not. You can add additional fields to a group by statement separated by commas, e.g. group by Item_Type_Dependent__c, Line_Item_Number__c, which will cause the system to first group by Item_Type_Dependent__c, and then for each record in that grouping group them again by Line_Item_Number__c. You could then order the result by Line_Item_Number__c since every row would necessarily have a Line_Item_Number__c column, although multiple rows could have the same value for Line_Item_Number__c if they have different Item_Type_Dependent__c.

Note that you will run into the same problem with all the other fields you have in your select statement, so you may want to reconsider what data you are actually hoping to get back to figure out if you should be using an aggregate query or not.

  • Thanks for the reply-- to clarify then my goal is not achievable then? What I'm attempting to query here is a table of products and some values pertaining to each row and I'd like to group by Item_Type_Dependent__c value and then order that by Line_Item_Number__c (which is simply a number field that represents the row) – charlton hua Jan 7 '20 at 14:33
  • @charltonhua Think about it this way - if you have two records with Item_Type_Dependent__c = 1, and they have Line_item_Number__c = 2 and 4, should that row come before or after another row with one record with Line_Item_Number__c = 3? You could order by MIN(Line_Item_Number__c). – IllusiveBrian Jan 7 '20 at 14:55
  • thanks for the info, looks like none of this will work since the fields being selected are native currency fields and when using GROUP BY method, we must group all fields selected I guess – charlton hua Jan 7 '20 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.