SUM aggregation do not work on empty subqueries with DRUID

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?

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.