0

I need to pull out a query that fetches:

From My Team object and Package object. Package is a child of Teams

From teams I need to pull how many packages is currently in teams and how much each packages weigh in total:

SELECT SUM(Weight__c) TotalWeight, COUNT(id) numOfPackages, 
Team__r.Phone, Team__r.Name,Team__r.id FROM Package__c GROUP BY 
Team__r.Phone, Team__r.Name,Team__r.Id

But I also need to know the last package that was added to the team:

SELECT Id,  (SELECT Id, Name, CreatedDate, weight__c FROM Package__r 
ORDER BY CreatedDate DESC LIMIT 1) FROM Team__c

How, if possible, could I combine these two queries?

2 Answers 2

1

Are you using a Master-Detail relationship between Teams and Packages? If so you can utilize a Roll-up field to both sum the weight of all packages, as well as show a count of all packages. These fields would exist on the Teams object.

If you are not using a Master-Detail relationship you might consider using the Declarative Lookup Rollup Summary tool to accomplish the same thing.

Then you could use the query you already have to get the most recent package, with the two new roll-up fields in the parent part.

SELECT Id, Total_Packages__c, Packages_Sum_Weight__c, (SELECT Id, Name, CreatedDate, weight__c FROM Package__r ORDER BY CreatedDate DESC LIMIT 1) FROM Team__c

2
  • Unfortunately I am not. Commented Jul 17, 2018 at 16:51
  • @kh9876 In that case I think your best option is to use your second query remove the limit clause, and loop through all records to calculate the total weight.
    – dBeltowski
    Commented Jul 17, 2018 at 17:42
1

You can not do this in SOQL (or for that matter any query language).

The requirement need two types of information:
1) Sum of Weight__c along with other details
2) Most resent package

Both above result need different sets of data so this can not be combine into one query.

You must log in to answer this question.

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