0

How can i rewrite proper SOQL query to get the below effect -

SELECT 
    coalesce(min(LastModifiedDate), 'NULL') minimum, coalesce(max(LastModifiedDate), 'NULL') minimum, 
    count(LastModifiedDate) total_count 
FROM  
    (SELECT 
        LastModifiedDate 
    FROM ACCOUNT 
    LIMIT 10 
    WHERE LastModifiedDate > xxxx and LastModifiedDate < xxxx
    )

or at least

SELECT 
    min(LastModifiedDate) minimum, max(LastModifiedDate) minimum, 
    count(LastModifiedDate) total_count 
FROM  
    (SELECT 
        LastModifiedDate 
    FROM ACCOUNT 
    LIMIT 10
    WHERE LastModifiedDate > xxxx and LastModifiedDate < xxxx
    )

Any help here will be appreciated

| improve this question | | | | |
2

You would need to use aggregateresult. See Working With SOQL Aggregate functions

So it would be something like this:

AggregateResult groupedResults = [SELECT min(LastModifiedDate) min, 
                                         max(LastModifiedDate) max, 
                                         count(Name)
                                  FROM   Account
                                  WHERE  LastModifiedDate > xxxx && 
                                         LastModifiedDate < xxxx
                                  Group By Name
                                  LIMIT 10];

Also, fun fact you cannot do coalesce as described here: What is the coalesce() eqivalent?

| improve this answer | | | | |
  • Thanks for the quick response.. but this query fails with exception - MALFORMED_QUERY: Non-grouped query that uses overall aggregate functions cannot also use LIMIT and if i try GROUP BY with LastModifiedDate.. its throwing field 'LastModifiedDate' can not be grouped in a query call. I am new to SOQL or salesforce. – PraveenK Apr 17 '17 at 20:31
  • Ahh, yes it seems you cannot do limit without group by. Your issues stems from the fact that you cannot group by a DateTime field. Does it matter what field you're grouping on? I suggest Name. I'll adjust my answer. – Dan Wooding Apr 17 '17 at 20:39
  • Unfortunately i need to group by on LastModifiedDate. All i am trying to do is trying to split records into batches each of 100 (n) records using LastModifiedDate (order by).. so that i can pull them in batches. Couldn't understand the best way using SOQL – PraveenK Apr 17 '17 at 20:43
  • Yeah it looks like you can't do the groupings. I'm stuck maybe someone else has a better idea. – Dan Wooding Apr 17 '17 at 20:46
  • Nowhere on your example code you grouped by LastModifiedDate. Can you let us know what you're trying to do (edit the Original Post)? I built a solution but it does not group by Date as you wish to do. – Sebastian Kessel Apr 17 '17 at 20:48
1

From the docs:

You can't use a LIMIT clause in a query that uses an aggregate function, but does not use a GROUP BY clause.

AND

Some object fields have a field type that does not support grouping. You can't include fields with these field types in a GROUP BY clause. The Field object associated with DescribeSObjectResult has a groupable field that defines whether you can include the field in a GROUP BY clause.

There are basically 2 ways for you.

One:

You can do a simple SOQL order by Lastmodifieddate with a limit 100 and it will return you a list of records.

So the first record's lastmodifieddate will be min(lastmodifieddate) and that of the last one will be max(lastmodifieddate). List size will be the count.

Two:

You can do this using 2 SOQL queries.

First fetch the first 100 records using a simple SOQL with a limit 100.

SELECT Id FROM Account LIMIT 100

save the result in a list, lets say - accList.

Now, do your Aggregate query filtering the records using the list.

SELECT MAX(LastModifiedDate),MIN(LastModifiedDate), count(Id) FROM Account where Id IN :accList

Bonus point: There is a date function in SOQL - DAY_ONLY

This function returns the DAY part of the Datetime value. This function if used in a SOQL, the field can be grouped as well.

SELECT DAY_ONLY(LastModifiedDate), count(Id) FROM Account group by DAY_ONLY(LastModifiedDate) order by DAY_ONLY(LastModifiedDate) LIMIT 10

Notice here: The LIMIT 10 is not applied to the actual account list but on the aggregated rows.

So if you want to aggregate on the first 100 records, there is simply no way using single Aggregate Query.

| improve this answer | | | | |
  • Thanks for the response. Yes.. i wanted to know if there is any (hacky) way to aggregate on first or every hundred records using SOQL. Answers seems NO. I thought about the other logic which didn't seem right for me. – PraveenK Apr 18 '17 at 16:09
  • Approach two seems to do what you wrote in the question but using 2 queries. – Santanu Halder Apr 18 '17 at 18:16

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.