Hi i wanted to know if there is a way to do an UNION of two different questions. Currently i have a UNION query set up in SQL and wanted to know if i can replicate the same result in a question format (the db is a PSQL one):
WITH
divisi AS (
SELECT
a.company,
coalesce (SUM(company_price * (1 - company_discount / 100.0)), 0) AS discounted_price,
CASE WHEN c.id_corporate IS NULL THEN 'direct' ELSE 'corporate' END AS type
FROM
account a
LEFT JOIN
customer c ON c.id_account = a.id
FULL JOIN
corporate co ON co.id_account = a.id
RIGHT JOIN
property p ON p.id_customer = c.id
RIGHT JOIN
service s ON s.id_property = p.id
WHERE
s.category = 'ZAK'
AND s.created_shot BETWEEN {{date_from}} AND {{date_to}}
and a.company != 'TEST'
GROUP BY
a.company,
c.id_corporate
),
totals AS (
SELECT
'Total Direct' AS company,
SUM(discounted_price) AS discounted_price,
'direct' AS type
FROM
divisi
WHERE
type = 'direct'
UNION ALL
SELECT
'Total Corporate' AS company,
SUM(discounted_price) AS discounted_price,
'corporate' AS type
FROM
divisi
WHERE
type = 'corporate'
)
SELECT company, SUM(discounted_price), type
FROM (
SELECT * FROM totals
UNION ALL
SELECT * FROM divisi
) combined
GROUP BY company, type
ORDER BY
CASE
WHEN company = 'Total Direct' THEN 0
WHEN company = 'Total Corporate' THEN 1
WHEN type = 'direct' THEN 2
WHEN type = 'corporate' THEN 3
ELSE 4
END,
company;
I know it's possible in SQL (this is in fact a query in my metabase folder so i already made it and it's already a UNION query) what i wanted to know if i can replicate the same result in question form (by that i mean making the 2 separate questions and merge them with something like the UNION statement)
Also is it possibile to do CTEs in the question editor? Or also that is only available in SQL
I tried the visual editor and was unsuccessful, using full join.
A union option in the visual editor would make a huge difference. I needed to join legacy tables with tables from the new system and I needed to do it via sql(union).
Thank you so much for the quick answers. One last thing: is it possible to do multi choice "IS" filtering in SQL? Whenever i try to apply a multi choice filter into an SQL in the dashboard it says "Native question variables only accept a single value. They do not support dropdown lists or search box filters, and can't limit values for linked filters."
My desire was to recreate the queries i've done in pure SQL into the GUI editor because i saw that there i could use multi choice filtering. If you can confirm to me that this (by this i mean applying multi choice filtering in a SQL query inside the dashboard) is also not possible it would be very appreciated.