[SalesForce] Invalid field count for AggregateResult:

Aggregate count error – please assist and excuse this basic mistake.
I am trying to count a number from the query and then to use it in some arithmetic.
Tring to get RemainingMonthsDec throws the error:

System.SObjectException: Invalid field count for AggregateResult:
Trigger

Here is code – thanks

AggregateResult[] ActualizedAmount = [
    SELECT SUM(Amount__c )sum
    FROM Deal_revenue__c
    WHERE date__c >: StartDate
    AND Deal__c =: deal.id
    AND Amount__c != NULL
];
Decimal ActualizedDecimal = (Decimal) ActualizedAmount[0].get('sum');

AggregateResult[] RemainingRevMonths = [
    SELECT COUNT(id)
    FROM Deal_revenue__c
    WHERE date__c >: Thisday
    AND Deal__c =: deal.id
    AND Amount__c != NULL
];
Decimal RemainingMonthsDec = (Decimal) RemainingRevMonths[0].get('count');

Decimal RemainingAmount = deal.Campaign_Amount__c - ActualizedDecimal; 

Decimal ReaminingSplitAmount =  (RemainingAmount / RemainingMonthsDec);

Thanks for the solutions. Per my experience, a small change of making the alias something other than the exact same word used in the aggregate function makes tracking and understanding the meaning of the alias easier. Example:

AggregateResult[] RemainingRevMonths = [SELECT COUNT(id) countalias 
                                        FROM Deal_revenue__c 
                                        WHERE date__c >: Thisday 
                                        AND Deal__c =: deal.id 
                                        AND Amount__c != NULL];

Decimal RemainingMonthsDec = (Decimal) RemainingRevMonths[0].get('countalias');

Best Answer

You didn't alias the field. Either add a field alias (count), or get the first expression (expr0).

List<AggregateResult> aggregates = [SELECT count(Id) count FROM ...];

// OR

Decimal count = (Decimal)myAggregate.get('expr0');

Take a look at the Force.com SOQL and SOSL Reference for a more in depth explanation:

Using Aliases with GROUP BY

You can use an alias for any field or aggregated field in a SELECT statement in a SOQL query. Use a field alias to identify the field when you’re processing the query results in your code.

Specify the alias directly after the associated field. For example, the following query contains two aliases: n for the Name field, and max for the MAX(Amount) aggregated field.
SELECT Name n, MAX(Amount) max
FROM Opportunity
GROUP BY Name
Any aggregated field in a SELECT list that does not have an alias automatically gets an implied alias with a format expri, where i denotes the order of the aggregated fields with no explicit aliases. The value of i starts at 0 and increments for every aggregated field with no explicit alias.

In the following example, MAX(Amount) has an implied alias of expr0, and MIN(Amount) has an implied alias of expr1.

SELECT Name, MAX(Amount), MIN(Amount) FROM Opportunity GROUP BY Name

In the next query, MIN(Amount) has an explicit alias of min. MAX(Amount) has an implied alias of expr0, and SUM(Amount) has an implied alias of expr1.
SELECT Name, MAX(Amount), MIN(Amount) min, SUM(Amount)
FROM Opportunity
GROUP BY Name

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

This is untested so you may need to have play, however this should return a List of the RevenueWrapper classes which contains the 4 columns you are looking to use in your VF page:

public class RevenueController{

Map<String, RevenueWrapper> ResultList = new Map<String, RevenueWrapper>(); 

public RevenueController(){
    List<AggregateResult> result =  [
    SELECT FISCAL_YEAR(Date__c) Date, Type__c, sum(Amount__c) Amount
    FROM Revenue__c
    GROUP BY FISCAL_YEAR(Date__c), Type__c];

    for(AggregateResult ar : result){

        RevenueWrapper RW = new RevenueWrapper();

        //If the RevenueWrapper already exists grab it from the Map
        if(ResultList.containsKey(String.valueOf(ar.get('Date')))){
            RW = ResultList.get(String.valueOf(ar.get('Date')));
        }

        //Set the amount
        RW.setAmount(ar);

        //Save back to the Map
        ResultList.put(String.valueOf(ar.get('Date')), RW);
    }
}

public List<RevenueWrapper> getResults() {
    return ResultList.values();
}


public class RevenueWrapper{
    public String revenueDate { get; set; }
    public Integer budgetAmount { get; set; }
    public Integer actualAmount { get; set; }
    public Integer varianceFYPercent { set; }
    public Integer varianceFYAmount { set; }

    public RevenueWrapper(){

    }

    public void setAmount(AggregateResult ar){
        revenueDate = String.valueOf(ar.get('Date'));

            //Set the amount value based on type
        if(ar.get('Type__c') == 'Budget'){
            budgetAmount = Integer.valueOf(ar.get('Amount'));
        }else if(ar.get('Type__c') == 'Actual'){
            actualAmount = Integer.valueOf(ar.get('Amount'));
        }else{ //Catch all for a type that isn't recognised
            return;
        }

    }

    public Integer getVarianceFYAmount(){
        return actualAmount - budgetAmount;
    }

    public Decimal getVarianceFYPercent(){
        if(budgetAmount != 0){
            return Decimal.valueOf((Double.valueOf(actualAmount) / Double.valueOf(budgetAmount)) * 100).setscale(1);
        } else{
            return 0;
        }
    }
}

}

[SalesForce] the coalesce() eqivalent

No. There's no way to get the results you're looking for. SOQL is a very limited query language that isn't as robust as full SQL, although it handles relationships easier than SQL.

[SalesForce] Invalid field count for AggregateResult:

Best Answer

Using Aliases with `GROUP BY`

Related Topic

Best Answer

Using Aliases with GROUP BY

Related Solutions

[SalesForce] Split a single field into 2 columns for a VF page

[SalesForce] the coalesce() eqivalent

Related Topic

Using Aliases with `GROUP BY`