You need to sign in to do that
Don't have an account?
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 !!!!
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