Assuming I am executing the following two queries on Druid:

SELECT COUNT(DISTINCT field_1) FROM my_table WHERE field_2 = 10 SELECT COUNT(DISTINCT field_1) FROM my_table WHERE field_2 = 11

I tried to combine both counts in one single query with a sum aggregation like the following:

Select q1.totalcount + q2.totalcount FROM (SELECT COUNT(DISTINCT field_1) AS totalcount FROM my_table WHERE field_2 = 10) as q1, (SELECT COUNT(DISTINCT field_1) AS totalcount FROM my_table WHERE field_2 = 11) as q2

And it works well UNLESS one of the two counts returns 0 rows.

In that case I receive a `Query returned no data`

message and I don’t have any final result from the SUM query even if one of the two subqueries returned some results.

How can I solve this?

## Advertisement

## Answer

Use conditional aggregation with `CASE`

expressions:

SELECT COUNT(DISTINCT CASE WHEN field_2 = 10 THEN field_1 END) + COUNT(DISTINCT CASE WHEN field_2 = 11 THEN field_1 END) FROM my_table WHERE field_2 IN (10, 11);

Note that, in case there is a `field_1`

value that is common in both groups of `field_2 = 10`

and `field_2 = 11`

, it will be counted in both groups, just like your code.