function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Alex MakkAlex Makk 

How to get SUM per GROUP from AgregatedResult in trigger?

Hello there, I'm relatively new to Apex and just wondering how I can get SUM from AgregatedResult per GROUP in a trigger.  We have Products that have different types. I want to know SUM per Type. Currently I do it like this:
  
AggregateResult[] groupedResultsServicesLine = [SELECT QuoteId,SUM(TotalPrice)ExtendedCostSum, SUM(Ext_Price__c)ExtPriceSum, SUM(For_Taxes__c)Taxes
                                                   FROM QuoteLineItem 
                                                   WHERE QuoteId in: QuoteIDs 
                                                   AND Product2.Type__c = 'Services'
                                                   AND Copied_from_Bundle__c = NULL 
                                                   GROUP by QuoteId];

As you see I filter by Product2.Type__c = 'Services' to get specific SUM for this type. Which means I have to do as many queries as we have types. I have a feeling there is a better way of doing that. I could GROUP BY Product2.Type__c but I'm not sure what is the syntax to get this value. Please advise. Thank you!
Best Answer chosen by Alex Makk
Maharajan CMaharajan C
Hi Alex,

Now Got your question:

You can remove your AND condition for the Product2.Type__c.

1. Set the Alias for the Product2.Type__c.
2. you can't access the Aggergate object values as like as normal object . To achieve this you have to use like Results.get('type') not as Results.Product2.Family.

AggregateResult[] groupedResultsServicesLine = [SELECT QuoteId,SUM(TotalPrice)ExtendedCostSum, SUM(Ext_Price__c)ExtPriceSum, SUM(For_Taxes__c)Taxes, Product2.Type__c  type   FROM QuoteLineItem WHERE QuoteId in: QuoteIDs AND Copied_from_Bundle__c = NULL GROUP by QuoteId,Product2.Type__c];


for(AggregateResult Results: groupedResultsServicesLine){
if(Results.get('type') == 'Services'){ 
....... 
}
}

Can you please Let me know if it helps or not!!!

If it helps don't forget to mark this as a best answer!!!

Thanks,
Maharajan.C

All Answers

Maharajan CMaharajan C
Hi Alex,

Your SOQL should be like below,

AggregateResult[] groupedResultsServicesLine = [SELECT QuoteId,SUM(TotalPrice)ExtendedCostSum, SUM(Ext_Price__c)ExtPriceSum, SUM(For_Taxes__c)Taxes FROM QuoteLineItem WHERE QuoteId in: QuoteIDs AND Product2.Type__c = 'Services' AND Copied_from_Bundle__c = NULL GROUP by QuoteId,Product2.Type__c];

Can you please Let me know if it helps or not!!!

If it helps don't forget to mark this as a best answer!!!

Thanks,
Maharajan.C
Alex MakkAlex Makk

Hi Majarajan! Thanks for your interest to this topic. I'm not sure you understood my request. There is no point of having : 

AggregateResult[] groupedResultsServicesLine = [SELECT QuoteId,SUM(TotalPrice)ExtendedCostSum, SUM(Ext_Price__c)ExtPriceSum, SUM(For_Taxes__c)Taxes, Product2.Family FROM QuoteLineItem WHERE QuoteId in: QuoteIDs AND Product2.Type__c = 'Services' AND Copied_from_Bundle__c = NULL GROUP by QuoteId,Product2.Type__c];
Because anyways query will return just SUMs for products that are 'Services' Type.

I'm looking into having this query that excludes AND Product2.Type__c = 'Services' as I want other types.
AggregateResult[] groupedResultsServicesLine = [SELECT QuoteId,SUM(TotalPrice)ExtendedCostSum, SUM(Ext_Price__c)ExtPriceSum, SUM(For_Taxes__c)Taxes, Product2.Family FROM QuoteLineItem WHERE QuoteId in: QuoteIDs AND Copied_from_Bundle__c = NULL GROUP by QuoteId,Product2.Type__c];

And then somehow I need to get SUM result JUST for services like: 

for(AggregateResult Results: groupedResultsServicesLine){
if(Results.Product2.Family == 'Services'){ 
....... 
}
}

Highlighted in bold is incorrect. I'm not sure how it should be written. Compile Error: Variable does not exist: Product2
Maharajan CMaharajan C
Hi Alex,

Now Got your question:

You can remove your AND condition for the Product2.Type__c.

1. Set the Alias for the Product2.Type__c.
2. you can't access the Aggergate object values as like as normal object . To achieve this you have to use like Results.get('type') not as Results.Product2.Family.

AggregateResult[] groupedResultsServicesLine = [SELECT QuoteId,SUM(TotalPrice)ExtendedCostSum, SUM(Ext_Price__c)ExtPriceSum, SUM(For_Taxes__c)Taxes, Product2.Type__c  type   FROM QuoteLineItem WHERE QuoteId in: QuoteIDs AND Copied_from_Bundle__c = NULL GROUP by QuoteId,Product2.Type__c];


for(AggregateResult Results: groupedResultsServicesLine){
if(Results.get('type') == 'Services'){ 
....... 
}
}

Can you please Let me know if it helps or not!!!

If it helps don't forget to mark this as a best answer!!!

Thanks,
Maharajan.C
This was selected as the best answer
Alex MakkAlex Makk
Thank you Maharajan!

I used following:
  
if(String.valueOf(Results.get('Type')) == 'Some Family'){
do something....
}