Is it possible do UNION in custom question?

I have native query like below
SELECT ‘Free’, count(location) FROM warehouse WHERE pallet_id IS NULL
UNION
SELECT ‘Used’, count(location) FROM warehouse WHERE pallet_id IS NOT NULL
Then I can have pie chart to see “free space in warehouse”.
Is it possible to get same result using custom question (and customer expression) ?

Hi @CZvacko
Yes, for the databases that supports it - full outer join:
https://www.metabase.com/docs/latest/users-guide/custom-questions.html#joining-data

Hi @flamber
DB is MySQL, it doesn’t support it.
So, no other way and have to stay with native query, right ?

@CZvacko Correct.

I just played with MSSQL DB that supports full outer join (warehouse self-joined like: where RowId=RowId). But I still don’t know how to define “Sumarize”, to get 2-rows answer applicable to pie chart.

@CZvacko I think you could probably do what you want with Custom Expressions:
https://www.metabase.com/blog/custom-expressions/index.html
But it’s not possible to compare to a null value in functions:
https://github.com/metabase/metabase/issues/12393 - upvote by clicking :+1: on the first post

You would do something like this (if you have some other way of not using null):

@flamber
Ok, null is issue…
BTW, I can’t find case in 0.36.1, no matter what DB is used for question :hushed:

@CZvacko https://github.com/metabase/metabase/issues/12375