Need guidance for using Group by clause in apex class

Question

I am newbie so for the first time i am using group by clause.But i am not getting where i am going wrong.

I have a VF Page where there is a status field which is a multi-picklist and a record type field which is also a picklist along with other fields like date which all are criteria.When user enters the criteria and clicks on run report the pdf or xls based on which report type user selects.

The original query which works for all record type is as below

String query = 'Select Strategy__c,Deal_Currency__c,Sector__c,Fund_Size_Millions__c,Geography_New__c,Point_of_Contact_lookup__c,Point_of_Contact_lookup__r.name,Timing_Quarter__c,Timing_Year__c,Total_Leverage__c,Description__c,Deal_Comments__c,Source1__r.name, Term_Sheet_Provided__c, Status__c,Analyst1__r.name, Sr_Leverage__c, Sponsor__c,Sponsor__r.name, Source_Contact2__c, Source_Contact1__c, Seller__c, Revenue_mm__c, Name, Mezz_Tranche_Amount__c, EBITDA_mm__c, Deal_source__c, Date_Received__c, Banker__c, Analyst3__c, Analyst2__c, Analyst1__c,Priority__c,Timing__c, Transaction_Type__c,Deal_source__r.name From Log__c';

query += whereClause;
query += ' order by Date_Received__c asc, name asc';

Now the requirement is when the user selects the one of the record type in it called Mezz record type...i have to group by status and subtotal on 5 amount fields and i have display subtotal fields in VF page rendered as pdf/xls.So i have written separate query for that.I dont know whether using the separate query is the best solution,please correct me if i am wrong.Below is my query.

String groupDealStatus;
if(isMezzRecordType)
{
    groupDealStatus='SELECT Status__c,SUM(Equity_amount__c) equitySum,SUM(Equity_Payment_Amount__c) equityPayment,SUM(Debt_Payment_Amount__c) debtAmount,SUM(Debt_Amount__c) Debt,SUM(Japan_Debt_Amount__c) JapanDebtAmount From Log__c ';
    groupDealStatus+= whereClause;
    groupDealStatus+= ' GROUP BY Status__c';
    System.debug('****groupDealStatus*****'+groupDealStatus);
    AggregateResult[] totalDealResults= Database.query(groupDealStatus);
}

Is this the correct way of performing group by?Pdf VF page is not rendering when i select more than 1 picklist value from status multi-picklist.If i have to write wrapper class for this how i can proceed with it.

Please help.

Thanks.

Answer 1

Given query returns aggregated result for 5 fields and groups by field status, where Status and recordtype is in condition.

SELECT Status__c,
SUM(Equity_amount__c) equitySum,
SUM(Equity_Payment_Amount__c) equityPayment,
SUM(Debt_Payment_Amount__c) debtAmount,
SUM(Debt_Amount__c) Debt,
SUM(Japan_Debt_Amount__c) JapanDebtAmount 
FROM Log__c
WHERE Status__c in ('ok','nok')
AND ( recordtype.name = 'recname' OR recordtypeId = '0xxxxxxxxxxxx')
GROUP BY Status__c

If you don't need to display recordtype - should work. Try this in query editor, if it's what you need, it will work in VF page. Every time you gonna to generate pdf, you should run query.