SOQL with GROUP BY & ORDER BY

Question

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

Answer 1

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.