Create Datatable with multiple queries

[NightWalker]

Hello,

I want to create a datatable from multiple queries where each of them have a different WHERE Clause. Each query should be shown as a new column which are grouped by a daily histogram.

I’m pretty sure that it should be something like this but in the example below the col2 and col3 tables are getting the value "null". Is something like this possible?

	filters 
	| essql 
	  query="SELECT SUM(number_of_items) as item_count FROM \"db_billing_*\"  WHERE customerGroup ='mycustomer' AND error_code=0 GROUP BY HISTOGRAM(\"@timestamp\", INTERVAL 1 DAY) ORDER BY HISTOGRAM(\"@timestamp\", INTERVAL 1 DAY) DESC"
	| mapColumn name="col2" fn={filters |essql query="SELECT COUNT(id) as myCount FROM \"db_billing_*\"  WHERE customerGroup ='mycustomer' AND error_code=0 AND customerID<1000 GROUP BY HISTOGRAM(\"@timestamp\", INTERVAL 1 DAY) ORDER BY HISTOGRAM(\"@timestamp\", INTERVAL 1 DAY) DESC"}
	| mapColumn name="col3" fn={filters |essql query="SELECT COUNT(UNIQUE(customerID)) as myCount2 FROM \"db_billing_*\"  WHERE customerGroup ='mycustomer' AND error_code=0 AND location<>'test' GROUP BY HISTOGRAM(\"@timestamp\", INTERVAL 1 DAY) ORDER BY HISTOGRAM(\"@timestamp\", INTERVAL 1 DAY) DESC"}
	| table 
	| render

[brianseeders]

Hello, @NightWalker,

mapColumn calculates a value using the data in the other cells in the same row, which I think is why you're getting null.

I think that you essentially need SQL UNION functionality to achieve what you need, which unfortunately is not currently supported. See related GitHub issue here: Elasticsearch SQL Support for UNIONs · Issue #34414 · elastic/elasticsearch · GitHub

It may be worth chiming in with your use case.

Thanks,
Brian