Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
MriduMridu 

Issues regarding Group By clause for getting subtotal for each product family

 

Hi,

 

I was trying to display the subtotal of product price for each product family.eg:A

A------- Product Family

a1-Product   p1-Price for product1

b1-Product2 p2-Price for product2

-----------------------

                         p3-Subtotal

B-Product family

b1-Product   p1-Price for product1

b2-Product2 p2-Price for product2

-----------------------

                         p3-Subtotal

 

I tried using Group By clause like this

 

for(QuoteLineItem Qli1:[select SUM(SubTotal),ProdFamily__c from QuoteLineItem where QuoteId = :quoteid GROUP BY (ProdFamily__c)])
        {
            amttt= amttt + Qli1.SubTotal;
          
        }  

But It is showing

Loop variable must be an SObject or list of AggregateResult error.please help me in this

Best Answer chosen by Admin (Salesforce Developers) 
vanessenvanessen

because you are not retrieving the subtotal field, its the sum of the field that your retrieving that's why it does not recognise the subtotal field. Try to use aliasing : 

 

List<aggregateResult> results = [select PricebookEntry.Product2.Family,SUM(Subtotal) stotal from QuoteLineItem where QuoteId = :quoteid group by PricebookEntry.Product2.Family];


        for (AggregateResult ar : results)
        {
         System.debug('TOTAL'+ar.get('stotal'));
           }

All Answers

vanessenvanessen

to be able to use SUM(subtotal) in your select , you will have to use AggregateResult and not the object type : QuoteLineItem.

AggregateResult is an object type that will capture the result.

 

try this:

 

 

for(aggregateResult results:[
  select SUM(SubTotal),ProdFamily__c
  from QuoteLineItem
  where QuoteId = :quoteid
  GROUP BY ProdFamily__c])  {
  amttt= amttt + results.SubTotal;
          
        } 

 

 
vanessenvanessen

for more info on AggregateResult ,check this link by jeff douglass : 

http://blog.jeffdouglas.com/2010/04/12/using-aggregateresult-in-salesforce-com-soql/
MriduMridu

Thanks for the solution.

I tried the second solution.

 List<aggregateResult> results = [select PricebookEntry.Product2.Family,SUM(Subtotal) from QuoteLineItem where QuoteId = :quoteid group by PricebookEntry.Product2.Family];
        for (AggregateResult ar : results)
        {
         System.debug('TOTAL'+ar.get('Subtotal'));
           }

I can save the class file.but in visual force page  its showing

System.SObjectException: Invalid field Subtotal for AggregateResult
vanessenvanessen

because you are not retrieving the subtotal field, its the sum of the field that your retrieving that's why it does not recognise the subtotal field. Try to use aliasing : 

 

List<aggregateResult> results = [select PricebookEntry.Product2.Family,SUM(Subtotal) stotal from QuoteLineItem where QuoteId = :quoteid group by PricebookEntry.Product2.Family];


        for (AggregateResult ar : results)
        {
         System.debug('TOTAL'+ar.get('stotal'));
           }

This was selected as the best answer
MriduMridu

Thanks for the solution.nw im able to view the subtotal
MriduMridu

i want to display that aggregate result ar.get('subtotal') to one specific field(currency),hw to convert the object type to decimal type.i tried using Decimal.valueof bt showing error

 

 
vanessenvanessen

have you tried Double instead of decimal??
MriduMridu

 

I was trying like this

List<aggregateResult> results = [select PricebookEntry.Product2.Family pdtfamily,SUM(Subtotal) stotal from QuoteLineItem where QuoteId = :quoteid group by PricebookEntry.Product2.Family];
        List<QuoteLineItem> qli=[select ProductSubTotal__c,ProdFamily__c from QuoteLineItem where QuoteId = :quoteid];
        for (AggregateResult ar : results)

 for (AggregateResult ar : results)
        {
         System.debug('TTTTOTAL'+ar.get('stotal'));
         System.debug('Productfamily'+ar.get('pdtfamily'));
         for(Integer i=0;i<=qli.size();i++)
         {
              if(qli[i].ProdFamily__c.equals(ar.get('stotal')))
              {
              qli[i].ProductSubTotal__c= Double.valueof(ar.get('stotal'));
         }
          
           update qli[i];
           
           
           }}

 

aggregate result returns value of object type-hw can i asssign to currency field
vanessenvanessen

of what type is qli[i].ProdFamily__c ??
MriduMridu

String type
vanessenvanessen

why are you checking if the prductfamily of type string equals to the total which should nornally return a double if properlly cast.

1.Is there a good reason for this???

2.what is the logic behind doing this.???

3.what do you really want your if condition to do???

:-)
MriduMridu

Sorry ..not total its family

for (AggregateResult ar : results)
        {
        
     
         System.debug('Productfamily'+ar.get('pdtfamily'));
         for(Integer i=0;i<=qli.size();i++)
         {
           
             
            
            
              if(qli[i].ProdFamily__c.equals(ar.get('pdtfamily')))
             {
                 qli[i].ProductSubTotal__c=Decimal.valueOf(ar.get('stotal'));
         }
          
           update qli[i];
           
           
           }}
          
vanessenvanessen

try to use this :

qli[i].ProductSubTotal__c= (Double)ar.get('stotal');