+ Start a Discussion

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:

First Name
Second Name
SomeOne WithOutEvent


Thanks In Advance !!!!


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 :



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