This message was deleted.

Check if this helps: https://druid.apache.org/docs/latest/development/extensions-contrib/distinctcount.html

@Bibek Sahoo that didn’t work for, I got this error: `Could not resolve type id 'distinctCount' as a subtype of

org.apache.druid.query.aggregation.AggregatorFactory

Have you tried just using a group by, such as:

select count (distinct flags) from wikipedia

with tmp as (
 select flags, count(*) from wikipedia where flags is not null and flags != '' group by flags
)
select count(*) from tmp

Both statements above should produce the same result. The second statement generates a native GroupBy query type.

@John Kowtko mysql query works fine for me, my question how to struct that as a json and send to druid?

something like:

with tmp as (
 select user_id, devices, sum(clicks) sum_clicks
   from my_table
   where __time >= '2023-06-26T01:03:41.177-04:00' 
     and __time <= '2023-06-27T01:03:41.177-04:00' 
     and user_id = '123'
  group by user_id, devices
)
 select user_id, count(*) devices_count, sum(sum_clicks) sum_clicks
   from tmp
  group by user_id

You can always do an Explain Plan on the above query to get the native query version. You may have to filter out NULL and/or EMPTY device names from the CTE in order to not include those as distinct values.

for some reason it’s not applying this and it’s not seeing all the columns in the query. thanks for the suggestion, I’ve tried using explain query, but it got to a very complex query that is hard to convert into java code or struct a json from it in java

that's likely because

COUNT(DISTINCT <expr>)

is planned as a

count

on top of a subquery to compute the distinct values

@Gian Merlino would that mean that there is no other simpler way?!

Ahmad, can you just use SQL for your query? Or must it be a native (JSON-style) query? ref: SQL API - https://druid.apache.org/docs/latest/querying/sql-api.html

I didn’t know it was possible to send sql query, I’ve discovered this only today, atm I’m using this approach to use the the api you sent. It would have a down side of converting the sql to native query, so I would have to check if it still would be efficient with that or not, but mainly for my need to use the distinct count this seems to solve my problem

ah, yes I'd recommend using SQL for this. it's simpler