Salesforce SOQL query return all values including NULL

Question

I have a simple SOQL query:

select Count(ID), CampaignId, Campaign.Name 
from CampaignMember 
where CampaignId in ('701U0000000MVoQ', '701U0000000MLFR', '701U0000000MVoL') 
group by CampaignId, Campaign.Name

The result of the query brings back 2 records as there are contacts in two of the campaign member lists.

I would also like to bring back the 3rd record in which the count will be 0. Is there a way to do this in SOQL, I don't believe there is an ISNULL() function that can be used on the select.

Answer 1

Any special reason why you need an aggregate query? One way to do it would be to use relationships, like that:

SELECT Id, Name, (SELECT Id FROM CampaignMembers)
FROM Campaign
WHERE Id IN ('701U0000000MVoQ', '701U0000000MLFR', '701U0000000MVoL')

Later in code it can be accessed like that

List<Campaign> campaigns = [Id, Name, (SELECT Id FROM CampaignMembers)
FROM Campaign
WHERE Id IN ('701U0000000MVoQ', '701U0000000MLFR', '701U0000000MVoL')];

for(Campaign c : campaigns){
    System.debug(c.Name + ': ' + c.CampaignMembers.size());
}

Answer 2

You have to add a additional part to your soql query called "Having". Having is like a Where Clause

select Count(ID), CampaignId, Campaign.Name 
from CampaignMember 
where CampaignId in ('701U0000000MVoQ', '701U0000000MLFR', '701U0000000MVoL')
group by CampaignId, Campaign.Name 
HAVING COUNT(ID) >0

Having is like a Where clause but the HAVING clause only applies to columns that also appear in the GROUP BY.