[SalesForce] Use COUNT_DISTINCT(field) on REST SOQL API query

I'm currently trying to run the following SOQL query using the REST API:

SELECT Id, accountId, COUNT_DISTINCT(accountId) 
FROM Contact 
WHERE Phone LIKE '%" + CLI + "' 
  OR HomePhone LIKE '%" + CLI + "' 
  OR MobilePhone LIKE '%" + CLI + "'"

However I get 400 bad request returned every time. If I take COUNT_DISTINCT out I get the following result:

{
  "totalSize": 2,
  "done": true,
  "records": [
    {
      "attributes": {
        "type": "Contact",
        "url": "/services/data/v27.0/sobjects/Contact/003M000000LJ2233P"
      },
      "Id": "003M000000LJ2233P",
      "AccountId": "001M000000Op99999",
      "Account": {
        "attributes": {
          "type": "Account",
          "url": "/services/data/v27.0/sobjects/Account/001M000000Op99999"
        }
      }
    },
    {
      "attributes": {
        "type": "Contact",
        "url": "/services/data/v27.0/sobjects/Contact/001M000000O123456F"
      },
      "Id": "001M000000O123456F",
      "AccountId": "001M000000OpMj5555",
      "Account": {
        "attributes": {
          "type": "Account",
          "url": "/services/data/v27.0/sobjects/Account/001M000000OpMj5555"
        }
      }
    }
  ]
}

The end goal is to get a count of the number of the accounts for all contacts returned (i.e 15 contacts found from 3 different accounts).

Is that possible using COUNT_DISTINCT or will I have to use C# to process the result?

Best Answer

I think you're missing your GROUP BY in the SOQL query. Try debugging it in workbench.

Your SOQL should be 

SELECT Id, COUNT_DISTINCT(accountId) FROM Contact WHERE Phone LIKE '%" + CLI + "' OR HomePhone LIKE '%" + CLI + "' OR MobilePhone LIKE '%" + CLI + "'" GROUP BY Id
Related Topic