System.QueryException in aggregateResults

Question

I am trying to run this query in apex and run into following error.

    AggregateResult[] groupedResults = [select Campaign__r.id cid, Count(opportunity__c) oppcount, FORMAT(SUM(Opportunity__r.Amount)) oppAmt from Campaign_influence__c where opportunity__c!=null AND Campaign__c in :CampaignList group by Campaign__r.id ];

System.QueryException: Invalid field: 'Opportunity__r':

This query does run fine in developer console 'query editor'. It also works if I replace FORMAT(SUM(Opportunity__r.Amount)) with just SUM(Opportunity__r.amount). What am I doing wrong here?

Answer 1

The User you are running this query as almost certainly lacks read permission on the Opportunity__c field. Navigate to their Profile configuration (Setup > Manage Users > Profiles) and go to Object Settings > Campaign Influence. Make sure they have Read Access selected for Opportunity.

What you're trying to do should be supported otherwise. The documentation on FORMAT() states it supports aliasing and can be nested with aggregate functions:

The FORMAT function supports aliasing. In addition, aliasing is required when the query includes the same field multiple times. For example:

SELECT Id, LastModifiedDate, FORMAT(LastModifiedDate) formattedDate FROM Account

You can also nest it with aggregate or convertCurrency() functions. For example:

SELECT amount, FORMAT(amount) Amt, convertCurrency(amount) editDate, FORMAT(convertCurrency(amount)) convertedCurrency FROM Opportunity where id = '12345'
SELECT FORMAT(MIN(closedate)) Amt FROM opportunity