function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
KevSnellKevSnell 

SOQL Count - MALFORMED_QUERY: Grouped query cannot use child relationships in the SELECT list

Hi All,


I have the following query which I have tested and works in SoqlXplorer.

 

SELECT Member__c, (SELECT Joined_Date__c,Joined__c FROM Opportunities__r WHERE Joined_Date__c = LAST_N_DAYS:365) FROM Contact WHERE Member__c = TRUE LIMIT 1000

 

However, my issue is I want to count the results ie like the below but this query results in MALFORMED_QUERY: Grouped query cannot use child relationships in the SELECT list

 

SELECT Count(ID), (SELECT Joined_Date__c,Joined__c FROM Opportunities__r WHERE Joined_Date__c = LAST_N_DAYS:365) FROM Contact WHERE Member__c = TRUE LIMIT 1000

 

Can anyone help.


Thanks


Kev

 

Best Answer chosen by Admin (Salesforce Developers) 
TheIntegratorTheIntegrator

You can get the single count by removing group by clause and aggregated Join_Date__c field as below

 

SELECT COUNT_DISTINCT(Opportunity_Contact__c) From Opportunity Where Joined_Date__c = LAST_N_DAYS:365 AND Opportunity_Contact__r.Member__c = TRUE

 

I also changed count to count_distinct as there could be opportunities with same contact.

All Answers

TheIntegratorTheIntegrator

Can you try and rewrite the query as follows

 

SELECT Count(Contact__c), Joined_Date__c From Opportunities Where Joined_Date__c = LAST_N_DAYS:365 AND Contact__r.Member__c = TRUE Group By Joined_Date__c 

 

Let me know if you get the desired result
KevSnellKevSnell

Sorry I get the error invalid field Contact__c

 

This is because Contact__c doesn't exist in the Opportunity Object.  
TheIntegratorTheIntegrator

What's the contact lookup field in opportunities__c object? replace that with contact__c
KevSnellKevSnell

OK just looked at the structure and it seems to be Opportunity_Contact__c

 

So changed the below to this:

 

SELECT Count(Opportunity_Contact__c), Joined_Date__c From Opportunity Where Joined_Date__c = LAST_N_DAYS:365 AND Contact__r.Member__c = TRUE Group By Joined_Date__c 

 

However, now get the error doesn't understand relationship Contact__r

 

Thanks

Kev 
TheIntegratorTheIntegrator

You need to change it inside as well, 

 

SELECT Count(Opportunity_Contact__c), Joined_Date__c From Opportunity Where Joined_Date__c = LAST_N_DAYS:365 AND Opportunity_Contact__r.Member__c = TRUE Group By Joined_Date__c 
KevSnellKevSnell

Thank you that works, however, how do I get a single count as the Group By causes it to count results for each Joined_Date and I want the total for the last 365 days.

 

 
TheIntegratorTheIntegrator

You can get the single count by removing group by clause and aggregated Join_Date__c field as below

 

SELECT COUNT_DISTINCT(Opportunity_Contact__c) From Opportunity Where Joined_Date__c = LAST_N_DAYS:365 AND Opportunity_Contact__r.Member__c = TRUE

 

I also changed count to count_distinct as there could be opportunities with same contact.

This was selected as the best answer
KevSnellKevSnell

Thank you so much that was what I was looking for.
KevSnellKevSnell

Well I'm had to change the soql to this:

 

Public integer getJoined(){
    AggregateResult OppAmount = Database.countQuery('SELECT COUNT_DISTINCT(Opportunity_Contact__c) From Opportunity Where OwnerId = :userId AND Joined_Date__c = LAST_N_DAYS:365 AND Joined__c = TRUE AND Opportunity_Contact__r.Member__c = True');
    Integer Joined = integer.valueof(OppAmount.get('expr0'));
    return Joined;    
    }

I'm pulling the ownerid from a drop down now, however, the above gives me the error Illegal assignment from Integer to SOBJECT:AggregateResult

 

Any help would be appreciated.
TheIntegratorTheIntegrator

Have you tried


AggregateResult OppAmount = [SELECT COUNT_DISTINCT(Opportunity_Contact__c) From Opportunity Where OwnerId = :userId AND Joined_Date__c = LAST_N_DAYS:365 AND Joined__c = TRUE AND Opportunity_Contact__r.Member__c = True];

 
KevSnellKevSnell

That worked when I was quering the current user, however, now that I am putting the :userid in it's place I get the error.

 

Looking at the log it shows the below, looks like :userid is being changed to :tmpVar1 and that is then saying it found no rows.

 

14:59:24.177 (177851000)|SOQL_EXECUTE_BEGIN|[112]|Aggregations:0|select COUNT_DISTINCT(Opportunity_Contact__c) from Opportunity where (OwnerId = :tmpVar1 and Joined_Date__c = LAST_N_DAYS:365 and Joined__c = true and Opportunity_Contact__r.Member__c = true)
14:59:24.181 (181501000)|SOQL_EXECUTE_END|[112]|Rows:0
14:59:24.181 (181639000)|EXCEPTION_THROWN|[112]|System.QueryException: List has no rows for assignment to SObject
14:59:24.181 (181839000)|FATAL_ERROR|System.QueryException: List has no rows for assignment to SObject

 Not sure why!!
TheIntegratorTheIntegrator

That's strange as the query is supposed to return 1 row always, if records are not found, it returns 0. Could you put a system.debug message to display value of userid
KevSnellKevSnell

The strange thing is I have the below query which works and that uses the userid so I know it is being passed to the apex class.

 

Public integer getYes_Joining_Forms_Sent(){ 
     integer Yes_Joining_Forms_Sent = Database.countQuery('SELECT count() FROM Lead where OwnerId = :userId AND Status = \'Yes - Joining Forms Sent\' AND IsConverted = False AND List_Assignment__c != \'Corporate\' LIMIT 1000');
        return Yes_Joining_Forms_Sent;
    }

 
KevSnellKevSnell

Thinking about it the userid isn't passed until the page is loaded and the dropdown box is selected so to start with the result would be 0.
KevSnellKevSnell

Got it!!  The issue was because userid was blank - thank you so much.

 

I've changed the code to this and it now works.

 

Public integer getJoined(){
if (userid == null)
            return 0;
AggregateResult OppAmount = [SELECT COUNT_DISTINCT(Opportunity_Contact__c) From Opportunity Where OwnerId = :userId AND Joined_Date__c = LAST_N_DAYS:365 AND Joined__c = TRUE AND Opportunity_Contact__r.Member__c = True];
Integer Joined = integer.valueof(OppAmount.get('expr0'));
return joined;
}

 
KevSnellKevSnell

Just got to work out how to write the testmethod for it.