You need to sign in to do that
Don't have an account?

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
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
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 more info on AggregateResult ,check this link by jeff douglass :
http://blog.jeffdouglas.com/2010/04/12/using-aggregateresult-in-salesforce-com-soql/
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
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'));
}
Thanks for the solution.nw im able to view the subtotal
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
have you tried Double instead of decimal??
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
of what type is qli[i].ProdFamily__c ??
String type
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???
:-)
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];
}}
try to use this :
qli[i].ProductSubTotal__c= (Double)ar.get('stotal');