SOQL, how to return highly organized data

Question

This may be a long shot, so bear with me.

I have a query that returns a list of catagores (text strings) and a count of the number of times they occur:

Order Request,7842
Request,3976
Other,3247
...

Query:

SELECT Category__c, Count(Id) FROM Case WHERE CreatedDate = LAST_MONTH GROUP BY ROLLUP (Category__c)

It's working fine, however I need the data to be filtered more and currently have Perl script doing the job. I have a list of categories (about ~10 out of a total of ~16) of which the occurrences should be counted, and all occurrences of the other categories should dump into the "Other" category.

The script also organizes the final categories into a list in a specific order.

Is there any way to do this within the query rather than further processing the data with a script?

---

EX: The perl script takes data like the following:

Category1, 4
Category2, 6
Category3, 8
Category4, 2
Other, 7

And will re format it by combining certain categories into the "Other" category and ordering them in a certain way:

Category3, 8
Category1, 4
Other, 15

Answer 1

Not I can think of

There might be a way to get an ORDER BY working so that Other is always last (for example), possibly by adding a priority field or the like ... but it seems like from the output changes that you would be asking SOQL to perform some math based on business logic and I can't think of a way to get it to do that.

To put it another way, you are asking SOQL to get the count of Other in the system and that's what it will retrieve for you. If that number needs to change, that's logic/processing outside the query.

Answer 2

If your field is a picklist and you've ticked "sort it like that everywhere" it might be worth a shot adding ORDER BY Category__c to your query. To be honest I have no idea if it'll respect it ;) "sort in that order everywhere" is useful when you want to have a funnel chart component of Opportunity Stages - they really make sense to be sorted into a pipeline.

Another option would be to offload this mapping task on your friendly System Administrator ;) Check if Analytics API would work for your case (it has limits of returned rows for example) and if it does - build a report with nice bucket fields? They offer options to display unmapped values as other...