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.

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.

  • 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 Aug 16 '15 at 10:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.