+ Start a Discussion
krish@123krish@123 

Please help me its very urgent on aggregate result



Result = [SELECT Count(Id) Total , Count(Customer_Type1__c ) name FROM contact  where Lead_SourceteLOOKUP__c !=null And Customer_Type1__c ='Buyer'   Customer_Type1__c ='Seller' GROUP BY Lead_SourceLOOKUP__c ];  

I need to display count of records  on visualforce page 

but i have stuck here how can display data  on isualforce page block table  with using above query

if Customer_Type1__c ='Buyer' 

i need to show this count in 
<apex:column  headervalue ="Buyer " value="{!Result}
lly 
if Customer_Type1__c ='Seller' 
<apex:column  headervalue ="Selleer " value="{!Result}

How can i acheived this


below query is the sql query 
i need to convert soql

select s.Name, SUM(CASE WHEN c.type='Buyer' THEN 1 ELSE 0 END) as Buyers , SUM(CASE WHEN c.type='Seller' THEN 1 ELSE 0 END) as Seller, COUNT(c.ID) as Total   
from source s
left outer join contact c on c.SourceId = s.Id



Thanks in advance
Best Answer chosen by krish@123
William TranWilliam Tran
First, your results has to be of type AggregateResult so you query looks like this:
 
AggregateResult[] Result = [SELECT Count(Id) Total , Count(Customer_Type1__c ) name FROM contact  where Lead_SourceteLOOKUP__c !=null And (Customer_Type1__c ='Buyer'  OR  Customer_Type1__c ='Seller') GROUP BY Lead_SourceLOOKUP__c ];

but then I am not sure why you would need to have 2 counts also you are not including the field Lead_SourceLOOKUP__c which looks like the object itself (not sure if the spelling is right 'Sourcete'?).

Regardless, once you get results for the AggregateResult you need to convert to a list so that you can display in VF page. 

Here's an example:

You can cut and paste this in your org to test it out and follow the example to build you specific scenario.

Controller
public with sharing class TestController {

    public Summary[] Summaries { get; set; }

    public TestController() {
        AggregateResult[] results = [
            SELECT Name, Count(Id) Quantity FROM Opportunity GROUP BY Name
        ];
        Summaries = new List<Summary>();
        for (AggregateResult ar : results) {
            Summaries.add(new Summary(ar));
        }
    }

    // wrapper class to hold aggregate data
    public class Summary {
        public Integer Quantity { get; private set; }
        public String Name { get; private set; }

        public Summary(AggregateResult ar) {
            Quantity = (Integer) ar.get('Quantity');
            Name = (String) ar.get('Name');
        }
    }

}


VF Page
<apex:page controller="TestController">
    <apex:form >
        <apex:repeat value="{!Summaries}" var="summary">
            {!summary.Name}: {!summary.Quantity}<br/>
        </apex:repeat>
    </apex:form>
</apex:page>




 

All Answers

Akhil AnilAkhil Anil
Result = [SELECT Count()  FROM contact where Customer_Type1__c = 'Buyer'];

The above query will give you the count of records where the Customer_Type1__c field has the buyer value. If you need the count of both, the buyers and the sellers, then use the below query.

Result = [SELECT Count()  FROM contact where (Customer_Type1__c = 'Buyer' OR Customer_Type1__c = 'Seller')];
William TranWilliam Tran
First, your results has to be of type AggregateResult so you query looks like this:
 
AggregateResult[] Result = [SELECT Count(Id) Total , Count(Customer_Type1__c ) name FROM contact  where Lead_SourceteLOOKUP__c !=null And (Customer_Type1__c ='Buyer'  OR  Customer_Type1__c ='Seller') GROUP BY Lead_SourceLOOKUP__c ];

but then I am not sure why you would need to have 2 counts also you are not including the field Lead_SourceLOOKUP__c which looks like the object itself (not sure if the spelling is right 'Sourcete'?).

Regardless, once you get results for the AggregateResult you need to convert to a list so that you can display in VF page. 

Here's an example:

You can cut and paste this in your org to test it out and follow the example to build you specific scenario.

Controller
public with sharing class TestController {

    public Summary[] Summaries { get; set; }

    public TestController() {
        AggregateResult[] results = [
            SELECT Name, Count(Id) Quantity FROM Opportunity GROUP BY Name
        ];
        Summaries = new List<Summary>();
        for (AggregateResult ar : results) {
            Summaries.add(new Summary(ar));
        }
    }

    // wrapper class to hold aggregate data
    public class Summary {
        public Integer Quantity { get; private set; }
        public String Name { get; private set; }

        public Summary(AggregateResult ar) {
            Quantity = (Integer) ar.get('Quantity');
            Name = (String) ar.get('Name');
        }
    }

}


VF Page
<apex:page controller="TestController">
    <apex:form >
        <apex:repeat value="{!Summaries}" var="summary">
            {!summary.Name}: {!summary.Quantity}<br/>
        </apex:repeat>
    </apex:form>
</apex:page>




 
This was selected as the best answer