[SalesForce] Group By in Soql

I need to group data on the basis of two fields "description" and "createdDate" in soql.

Select Subject, WhatId, what.name, Owner.name, Owner.Id, IsVisibleInSelfService, ActivityDate, LastModifiedDate From Task Where whoId =: contactId Group By(Description, createdDate).

But it is throwing an error:

Unknown error parsing query

Best Answer

There are a few different issues with your code.

  • All fields in your SELECT clause must be in your GROUP BY clause or wrapped in an aggregate operation.
  • You can't GROUP BY a Datetime field (chances two records share the same value are extremely low). If you want to count just the date portion, use DAY_ONLY.
  • You have a trailing . character in your query. If that is not a typo and you did include it, it will cause your query to fail.

The following query should work, for example:

SELECT
    Description, DAY_ONLY(CreatedDate), count(Id),
    MIN(LastModifiedDate), MAX(LastModifiedDate),
    MIN(ActivityDate), MAX(ActivityDate),
    COUNT_DISTINCT(WhatId)
FROM Task
WHERE WhoId = :contactId
GROUP BY Description, DAY_ONLY(CreatedDate)

If you really need values from the other fields, you should question why you are grouping in the first place. If you want to include them in your SELECT clause, you need to either GROUP BY them as well or decide on the proper aggregation method to apply to them.

Related Topic