3

I seem to have run in to a SOQL error that I'm hoping someone has solved before.

I am creating a dashboard for UserLogins using the LoginHistory object

The query I am trying to run is:

SELECT COUNT_DISTINCT(userid) agg 
FROM loginhistory 
WHERE logintime = THIS_MONTH 
AND userid IN ('id1', 'id2', ...)

When I run the query I get back this agg result:

[ {
  "attributes" : {
    "type" : "AggregateResult"
  },
  "agg" : 1
} ]

What I need is the number of distinct logins within the time frame, however when I add a GROUP BY UserID salesforce errors:

"Grouped field should not be aggregated: UserId"

Query:

SELECT userid,COUNT_DISTINCT(userid) agg 
FROM loginhistory 
WHERE logintime = TODAY 
AND userid IN ('id1','id1') 
GROUP BY userid
  • IF you run: SELECT userid,COUNT_DISTINCT(userid) agg FROM loginhistory WHERE logintime = TODAY you get "MALFORMED_QUERY: Field must be grouped or aggregated: UserId" – ChrisBland Jul 23 '15 at 17:38
  • If you run: "SELECT userid,COUNT_DISTINCT(userid) agg FROM loginhistory WHERE logintime = TODAY GROUP BY UserId" you get MALFORMED_QUERY: SELECT userid,COUNT_DISTINCT(userid) agg FROM loginhistory WHERE ^ ERROR at Row:1:Column:30 Grouped field should not be aggregated: UserId – ChrisBland Jul 23 '15 at 17:38
  • Final comment: the reason I can't do a COUNT is each time you login you get a new LoginHistory record, we just are trying to identify if they did login during that time window – ChrisBland Jul 23 '15 at 17:40
  • I do not have an answer for the query but one way of getting what you want is to query the LoginHistory table and add the returned userId values to a Set which will remove duplicates. – Aayush K Jul 23 '15 at 18:11
  • Thanks but in super large orgs you'll blow out the record collection size, my user has 40+ login history objects for today only, so across multiple thousand people you can't query them all back so you must use the aggregate results – ChrisBland Jul 23 '15 at 18:53
2
SELECT Count(Id) agg, userId acc 
FROM LoginHistory 
GROUP BY UserId
| improve this answer | |

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.