8

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!

3
  • What is Category__c? Text, a Lookup?
    – EricSSH
    Aug 4, 2014 at 17:58
  • It is a text field Aug 4, 2014 at 19:13
  • Easy answer to the error, COUNT(ID) or any other field that is not part of your GROUP BY statement. Still, answer #2 below seems like the best option for what you are after. Because COUNT(ID) will give you the count of rows that match that category, versus the DISTINCT list of categories that exist and are being used in the table.
    – Shanerk
    Apr 1, 2022 at 17:50

3 Answers 3

6

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.

5
  • This works, but I'm not getting a count in the result; it is just creating a list of the category names... Aug 4, 2014 at 19:16
  • 1
    @RyanMcClure SELECT Count(Id), Category__c FROM Case WHERE CreatedDate = THIS_MONTH GROUP BY Category__c ORDER BY Category__c DESC?
    – sfdcfox
    Aug 4, 2014 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! Aug 4, 2014 at 19:27
  • Is there any way to insert an arbitrary row with the sum of all the counts? Aug 4, 2014 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, 2014 at 19:38
6

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
  • Yes this is 100% what you want. And for very large tables, using the COUNT(ID) GROUP BY method, you will get the dreaded queryMore() exception in Dev Console.
    – Shanerk
    Apr 1, 2022 at 17:46
-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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .