1

I just want to do a query of a custom object to sum the sales per day for every user. The idea is to show how much every person has sold, even if the result is zero I need to show that value.

Example User: John/ SUM= 10$ User Peter/ SUM= 0$ User Edward/ SUM=5$

This query return just the values that have a value in SUM (John and Edward) and I need to show the result for all the active users.

This is the query:

SELECT Owner.Name c, SUM(Total__c) s FROM Order__c WHERE (Owner.Id
IN:ActiveUsers) AND CreatedDate = Today group by Owner.Name order by Owner.Name ASC

ActiveUsers is a List of Ids which is correctly filled, in my case has 23 active users.

How can I show all the sums for every user and not like in this query that just show me if the SUM has a value.

Thanks a lot.

1 Answer 1

4

You can't retrieve zero values in an aggregate result query. You'll have to do two queries-- one to find all active users, and another to find the aggregate sums. Something like this:

 Map<Id, Decimal> totals = new Map<Id, Decimal>();
 for(User record:[SELECT Id FROM User WHERE IsActive = true]) {
     totals.put(record.Id, 0);
 }
 for(AggregateResult record:[SELECT Sum(Total__c), OwnerId FROM Order__c GROUP BY OwnerId]) {
     totals.put(record.get('expr1'), record.get('expr0'));
 }

Without pre-filling a map, you'll never get zero values in a query result, because Salesforce doesn't support a left-join type notation.

1
  • Thanks sfdcfox. I really appreciate your help. Your answer is just what I needed. I didn't know that Salesforce doesn't support LEFT JOIN
    – MANUELAN00
    Commented Aug 16, 2015 at 10:01

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .