Hi folks, hope you're good.
I'm trying to do the equivalent of a sub-query inside of the Notebook Editor, which I have been successful at doing with a written SQL query.
I am a beginner at both SQL and Metbase, so please forgive me if this question does not make sense.
What I am trying to do is simple, this is an example dataset:
id | number
1 | 2
1 | 2
2 | 4
2 | 4
Now in the Notebook Editor, when I SUMMARISE by selecting SUM of number, and then grouping by distinct ID, I get the total of 12. The total should be 6.
With an SQL query its easy because I can just write
SELECT SUM(number) AS total_sum
FROM (
SELECT DISTINCT id, number
FROM dataset
) AS final_sum;
But I am not able to achieve this in the Notebook Editor. The reason why I want it done in the Notebook Editor is because I have a dashboard with filters which I have not figured out how to connect to written SQL queries yet.
In addition, the source for the Questions in my Dashboard is a Model of two joined datasets, where one of them has elements that I would like to filter for, whereas the other one has the numbers. So I could in theory simple use the dataset which does not have duplicate IDs (which appear as a consequence of the join), but then I won't be able to filter by the contents of the joined dataset.
Any pointers?
Thanks