Skip to main content

Displaying Aggregate result on Visualforce using SOQL

There are some who jump out of bath-tub nearly naked when they are highly excited, fortunately I am not one of them, but when I saw the Aggregate functions in Spring 10 release, I was nearly in tears, ok, thats too dramatic but Aggregate functions giving a very beautiful meaning to data altogether.

For those of you who knew SQL you obviously know what aggregate functions are and how they beautify your code. Those who do not know, aggregate functions help you aggregate the date based on certain field.

Say for example, you want to show the count and Opportunities created on a day, you can aggregate them using SOQL.

AggregateResult[] groupedResults  = [SELECT Name, Count(Amount) FROM Opportunity GROUP BY CreatedDate];

So how do we display it on Visualforce:

Just iterate on the AggregateResults and get elements one by one.

for (AggregateResult ar : groupedResults)  {
    System.debug('Created Date' + ar.get('CreatedDate'));
}

A few pointers for debugging:

1. Always use API version 18.0+ while using Aggregate Functions. If you are eclipse, you might want to update it. And frankly if you are still using older version of eclipse, my mind wants to scream and ask you...why?

2. If you are Aggregating using a relationship e.g., Object1__r.object2__c in your SOQL, while fetching from AggregateResults you only need Object2__c, ar.get('Object2__c') I am not really sure why, because I found no documentation supporting this, but it worked for me in the code.


Thats it for today,
Happy Coding,
 How does a project get to be a year late?... One day at a time.
-- Fred Brooks


P.s. Did you see the new symbol I created for the site? Star wars and cloud in one icon. :D

Comments

  1. List results = [SELECT Date__c, MeasureID__c,SUM(MeasureValue__c) totalMeasureValue
    FROM UserAdoption__c GROUP BY Date__c,MeasureID__c ORDER BY Date__c];

    Date measureDate;
    String measureId;
    String measureName;
    Integer totalMeasureValue;
    for (AggregateResult ar : results){
    //Works:Below line works fine with out namespace
    measureDate= Date.valueOf(ar.get('Date__C'));
    //Not Working:But when i packaged it and give some namespace('XYZ') the above line fails
    // error:invalid field Date__C

    //Works:If prefix with namesapce the above line works

    measureDate= Date.valueOf(ar.get('XYZ__Date__C'));


    }

    ReplyDelete

Post a Comment

Popular posts from this blog

Some PDF tricks on Visualforce: Landscape, A4, page number and more

The beauty of Visualforce is simplicity. Remember the shock you received when you were told the entire page renders as PDF if you just add renderAs=PDF to the Page tag. For those who thought I spoke alien language right now, here is the trick, to render a page as PDF, we add a simple attribute to the <apex: page> tag <apex: page renderAs='pdf'> This will render the entire page as PDF. Now, say we need to add some extra features to the PDF. Like a page number in the footer or we need to render the page in landscape mode. Faced with this problem, I put on my Indiana Jones hat and went hunting for it in the vast hay-sack of the internet (read: googled extensively). Imagine my happiness when i found a big big page with many big big examples to solve the problem. The document I am referring to is from W3C, paged Box media . Long story short, I now possess the ultimate secret of rendering the page in any format I want. So here are few tricks I learned from the p

Top four design patterns and algorithm that every Salesforce Developer should know

A few days ago I was speaking to a developer friend, he was asked about a Singleton design pattern and he had no idea about. He spoke about how we developers never talk about patterns. And that time it hit me, we developers do not talk about design patterns and algorithms. There are many design patterns available off the shelf, they talk in detail about the problems they can solve. However, in my experience, I have come across and used the following 1. Singleton This is one of the most common and important design pattern to be used. When initiating objects, we need to make sure only one instance of the object is initiated. In a multi-tenant system like Salesforce, it becomes imperative that we do not hog more resources than needed Lets look at an example trigger AccountTrigger on Account (before insert, before update) { for(Account record : Trigger.new){ AccountMasterRecordType rt = new AccountMasterRecordType (); .... } } public class AccountMaster

The unofficial guide to become a Certified Salesforce Administrator (ADM 201)

In my attempt at maximum certifications in 60 days, I completed Salesforce Certified Administrator exam on February 11th 2013 So you have decided to ramp up your career and take certifications in your hand. Good choice. It is also likely that this is the first time you have heard of Salesforce, certification and since your company has a vision of you completing the certification you have decided to do it. At this stage it is likely that, You have done extensive googling. You have received countless brain-dumps. And you have received plenty of advise from different types of users which ranges from Admin certification is easier than making coffee to Admin certification is tougher than building a rocket-ship to fly off to the moon. The purpose of this guide is to give you a clear understanding of what to expect when you are expected to become Certified Salesforce Administrator. To bring sense to all the things you have seen so far and to clearly explain what to do and what