4

I have to pull data from a Salesforce database and get a count of the number of times each category appears. Currently, I have it pulling a list of the categories and then I use a perl script to filter through them and count, etc... but I would like to do it all through the SQOL query. This is what I'm trying currently, but it isn't working:

Select Category__c, COUNT(Category__c) 
FROM Case 
WHERE CreatedDate = THIS_MONTH 
GROUP BY Category__c

I keep getting the error "Grouped field should not be aggregated: Category__c"

I'm new to SQL/SOQL so I'm sure there is a simple solution for this. I've searched for similar questions but haven't found a solution yet...

A few other things I would like to do if possible:

  • Return a row with "Total:" and the total count of categories
  • Order the results in descending order

Any help is appreciated!

  • What is Category__c? Text, a Lookup? – EricSSH Aug 4 '14 at 17:58
  • It is a text field – Ryan McClure Aug 4 '14 at 19:13
4

Use COUNT(Id) to get a record count per category.

To order the results, use ORDER BY Category__c DESC.

You can't easily return a count of distinct categories returned, but really all you need here is to count the number of rows returned.

  • This works, but I'm not getting a count in the result; it is just creating a list of the category names... – Ryan McClure Aug 4 '14 at 19:16
  • @RyanMcClure SELECT Count(Id), Category__c FROM Case WHERE CreatedDate = THIS_MONTH GROUP BY Category__c ORDER BY Category__c DESC? – sfdcfox Aug 4 '14 at 19:21
  • Sorry, I'm using the dataLoader and realized I didn't account for the new row so I need to make some changes to process-conf and update the mapping file. The query works fine on the dataLoader GUI. Thanks! – Ryan McClure Aug 4 '14 at 19:27
  • Is there any way to insert an arbitrary row with the sum of all the counts? – Ryan McClure Aug 4 '14 at 19:29
  • @RyanMcClure Sure, you can change it to: SELECT Count(Id), Category__c FROM Case WHERE CreatedDate = THIS_MONTH GROUP BY ROLLUP (Category__c) ORDER BY Category__c DESC -- You'll a row with "category__c = null" with the sum of all values. – sfdcfox Aug 4 '14 at 19:38
1

COUNT_DISTINCT will give a count of the distinct, non-null values in a field, which is what the title of the question suggests is required:

SELECT COUNT_DISTINCT(Company) FROM Lead

https://developer.salesforce.com/docs/atlas.en-us.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_agg_functions.htm#fn_distinct

@sfdcfox's solution with GROUP BY ROLLUP will give the totals.

-1

Topic's a bit old but in case someone runs into the same...

There are a few options:

SELECT count(Id),Category__c FROM OBJECT GROUP BY Category__c -> this will tell you how many records in each category, but you may run into an error depending on the number of records

SELECT count(Id),Category__c FROM OBJECT GROUP BY ROLLUP Category__c -> this will tell you how many records in each category, the total number of records, plus won't run into errors due to the number of records.

SELECT count_distinct(Category__c) FROM Object -> this will give you a count of how many values are in the Category__field, but will not list what those values are.

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.