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)
insert into ContactTable select 1,'First Name'
insert into ContactTable select 2,'Second Name'
insert into ContactTable select 3,'SomeOne WithOutEvent'
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:
Thanks In Advance !!!!