How to find the number of consumers using query

I am trying to find the number of consumers. Right now I have the following query:

SELECT count(*) from Transaction where appName=‘application1’ or appName= ‘application2’ or appName= ‘application3’ facet rootConsumerName SINCE 24 hours ago Limit MAX

When I run the query, each consumer is returned with their number of transactions. Instead of getting a list of each consumer and their transactions, how do I get the number of consumers that were returned? For example, the query above returns 5 different consumers, so I would like 5 to be the result.

I know this would be an easy solution if this was done with Python, however, I am not too familiar with writing queries and am not too sure how to figure out a problem like this.

SELECT uniqueCount(rootConsumerName) from Transaction where appName in (‘application1’, ‘application2’, ‘application3’) SINCE 24 hours ago

Be careful with this though. UniqueCount() doesn’t promise to be accurate. If you have millions it will get you maybe close, but don’t bank on it.

If you only want the list of rootConsumerName then use:

SELECT uniques(rootConsumerName) from Transaction where appName in (‘application1’, ‘application2’, ‘application3’) SINCE 24 hours ago

Looks like @6MM and @stefan_garnham has given you some great tips. @Gwiggins let us known how this worked out for you! :slight_smile:

1 Like