+ Start a Discussion
MichnikMichnik 

SOQL: How to get average of some customField in standard LEFT JOIN query

Hi there!

I have two objects:
- Contact - Standard object
- Event__c - my custom object with field Integer RatingInt__c
I have Master -detail relation in Event__c :

API Name:  Contact__c

Child Relationship Name: Events

Related To: Contact


create table ContactTable (IdContactTable int, Name varchar(40))I created two query:
1. [SELECT c.Contact__r.Id,c.Contact__r.Name ContactName, AVG(RatingInt__c) AvgRating from Event__c c group by c.Contact__r.Id,c.Contact__r.Name ] - but this will show only contacts which already have Event
2. [SELECT Id, FirstName, (Select RatingInt__c from Events__r) a, c.Name from Contact c] - but i can't use agregate function AVG() in subquery.


I have to show all contacts with average from events. Where contact doesn't has any events i want to get null.

Maybe i will show what kind of query i want to have in SQL:

create table CustomEvent (IdCustomEvent int,IdContactTable int, Rating int)

--fill ContactTable
insert into ContactTable select 1,'First Name'
insert into ContactTable select 2,'Second Name'
insert into ContactTable select 3,'SomeOne WithOutEvent'

--fill CustomEvent
insert into CustomEvent select 1,1, 5
insert into CustomEvent select 2,1, 3
insert into CustomEvent select 1,2, 1

select Name, AVG(Rating) Rating
from ContactTable ct
left join CustomEvent ce on ce.IdContactTable = ct.IdContactTable
group by ct.name


drop table ContactTable
drop table CustomEvent


OUTPUT from this sql query:

Name
First Name
Second Name
SomeOne WithOutEvent
Rating
4
1
Null

 

Thanks In Advance !!!!

salvatoreovlassalvatoreovlas

Firs of all SOQL is not SQL and trust me it took me time to realize it!!! :)

Anyway what you need is aggregate functions, please refer to :

http://www.salesforce.com/us/developer/docs/apexcode/Content/langCon_apex_SOQL_agg_fns.htm

 

Hope this helps! ;) if yes mark it as a solution