Apex Code Development You need to sign in to do that
Don't have an account?
hideawayguy Aggregate Result set with values from multiple queries
Hi everyone I'm trying to return an aggregate result to pass to visualforce charts with this controller. i need to sum multiple datapoints with different where clauses from the same object.
this is the error i get...
Error: Compile Error: Didn't understand relationship 'Booking__c' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names. at line 7 column 13
Booking__c is the same object as the main query
any insights are greatly appreciated
public with sharing class BkgPerformance {
public List<AggregateResult> getData() {
// return
List<AggregateResult> working =
[SELECT COUNT(Id) theCount,
(select count(Id) from Booking__c where Booking_Stage__c = 'Pending') thePending,
(select count(Id) from Booking__c where Booking_Stage__c = '1st Payment'), theFirst,
(select count(Id) from Booking__c where Booking_Stage__c = '2nd Payment'), theSecond,
CALENDAR_YEAR(CreatedDate) theYear
FROM Booking__c
GROUP BY CALENDAR_YEAR(CreatedDate)
ORDER BY CALENDAR_YEAR(CreatedDate)];
return (working);
}
}
Hi hideawayguy,
The problem is within nested subqueries:
(select count(Id) from Booking__c where Booking_Stage__c = 'Pending') thePending,
(select count(Id) from Booking__c where Booking_Stage__c = '1st Payment'), theFirst,
(select count(Id) from Booking__c where Booking_Stage__c = '2nd Payment'), theSecond,
Instead of regular object name Booking__c You should use child relationship name ended with __r. Probably in Your case it will be Bookings__r.
Let me knwo whether it helped.
Best regards
hey thanks for the quick reply. it's actually the same obect as the main query though, so it's not a related object. i've tried it and it gives the same error. any other ideas?
thanks again for your help!
Sorry, I've missed that the main query is from the same object as subqueries. Unfortunately it is not possible in the way You wrote. However You can build it a little bit different having the same result:
List<AggregateResult> working =
[SELECT COUNT(Id) theCount, CALENDAR_YEAR(CreatedDate) theYear, Booking_Stage__c
FROM Booking__c
WHERE Booking_Stage__c in ('Pending', '1st Payment' ,'2nd Payment'')
GROUP BY CALENDAR_YEAR(CreatedDate), Booking_Stage__c
ORDER BY CALENDAR_YEAR(CreatedDate)];
Best regards and Happy Easter.
happy easter to you too! so i tried it but it only returns one count, i need seprate counts for each of the conditions. it also only returns data from the current year, rather than all years grouped by year. any other thoughts are greatly appreciated!
It should work properly. I've just build and runned the query which is a equivalent of the one which I've proposed to You:
System.debug('XXX' + [SELECT COUNT(Id) theCount, CALENDAR_YEAR(CreatedDate) theYear, LeadSource FROM Contact
GROUP BY CALENDAR_YEAR(CreatedDate), LeadSource
ORDER BY CALENDAR_YEAR(CreatedDate)]);
So You have above grouping by Created Date and some std picklist value.
As a result I've recevied:
XXX(AggregateResult:{theCount=3, theYear=2012, LeadSource=null}, AggregateResult:{theCount=1, theYear=2012, LeadSource=Web}, AggregateResult:{theCount=1, theYear=2012, LeadSource=Purchased List}, AggregateResult:{theCount=2, theYear=2012, LeadSource=External Referral}, AggregateResult:{theCount=1, theYear=2012, LeadSource=Partner}, AggregateResult:{theCount=7, theYear=2012, LeadSource=Public Relations}, AggregateResult:{theCount=2, theYear=2012, LeadSource=Trade Show}, AggregateResult:{theCount=3, theYear=2012, LeadSource=Word of mouth}, AggregateResult:{theCount=1, theYear=2013, LeadSource=null}, AggregateResult:{theCount=2, theYear=2013, LeadSource=Web}, ...)
It should work also in Your case. Are You sure that You have data also from previous years in mentioned types of Booking_Stage__c ???
hello again!
so the results i need would look like this
the counts=176,
the year=2007,
theCountofPendingBookings=94,
theCountofFirstPaymentBookings=43,
theCountofSecondPaymentBookings=34
the counts=176,
the year=2008,
theCountofPendingBookings=94,
theCountofFirstPaymentBookings=43,
theCountofSecondPaymentBookings=34
i need unique counts for each of the booking_stages to pass to the charts so i can draw multiple lines on a line graph. does that make sense? sorry for the confusion if i wasn't clear. i really appreciate your help on a saturday!
Ok, I think I know how You can support it - however Im not sure whether You will like the solution :) But for sure it gonna work.
Because AFAIK You can not build a nested queries from the same object You can use some workaround. If You will add a number formula fields on Booking__c object like the ones below:
Formula1:
If( ISPICKVAL(LeadSource, 'Pending'),1,0)
Formula2:
If( ISPICKVAL(LeadSource, '1st Payment'),1,0)
Formula3:
If( ISPICKVAL(LeadSource, '2nd Payment'),1,0)
Then change Your query to the one below:
[SELECT COUNT(Id) theCount, CALENDAR_YEAR(CreatedDate) theYear, Booking_Stage__c, SUM(Formula1)thePending, SUM(Formula2)theFirst, SUM(Formula3)theSecond FROM Booking__c
GROUP BY CALENDAR_YEAR(CreatedDate), Booking_Stage__c
ORDER BY CALENDAR_YEAR(CreatedDate)];
I've checked it on my Contact example and it worked.
Alternative, better solution would be to use my previous query and try to calculates sums there, but Im not sure whether it is supported.
I hope I helped You a little bit.
Best wishes!
nice! very creative. that'll def work for this report. i still need at some point to figure out how to solve it without adding formula fields. but this is great. thank you so much. if you come across a solution in the future for summing without the formula fields, i'd LOVE to know.
thanks again, happy easter.