• Michnik
  • 0 Points
  • Member since 2012

  • Chatter
  • 0
    Best Answers
  • 0
    Likes Received
  • 0
    Likes Given
  • 1
  • 0

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 !!!!

  • September 26, 2012
  • Like
  • 0