Addition of two questions

Hello,

I have two questions, one is the amount of delivery and the other is the amount of credit note. Currently I am displaying this result on a dashboard with two widgets.

Is it possible, with the GUI, to make only one which would be an operation of the result of these two questions or to make it in a question?

Thank's

Hi @flipflip
There's different ways of combining into a single question:
https://www.metabase.com/docs/latest/users-guide/09-multi-series-charting.html#combining-two-saved-questions
or
https://www.metabase.com/docs/latest/users-guide/referencing-saved-questions-in-queries.html

Hi @flamber,

I am in the second case. But my problem is that there is no link between the result of the question on delivery slips and the result of the question on credit notes.

So I don't understand how to do with the GUI (even natively in sql) to subtract the result of these two questions ?

@flipflip If you want to use the GUI, then you would need some column to join with.
If you are using SQL, then you can do it with a CTE:
https://www.metabase.com/learn/sql-questions/sql-cte

@flamber,

Ok, I just did a test with a simple query

WITH livraison(total_liv) AS (
    SELECT sum(s.qty) FROM sdeliveryd AS s
    inner join sdelivery AS s1 on s.sdhnum = s1.sdhnum 
    WHERE s1.bpcord = "020050"
),
avoir(total_av) AS (
    SELECT sum(s.amtnot) FROM sinvoice AS s
    inner join sinvoicev AS s1 on s.num = s1.num
    WHERE s1.bpcinv = "020050"
)

SELECT * from livraison;

I get a result with

SELECT * from livraison

But how to use the result of "avoir"?

@flipflip So since you have a column that matches, then you could return that and do joins with it.
There are a lot of possibilities, but to keep it simple:

select (select sum(qty) from table where something=1) / (select avg(col) from table2 where different=2)

It's just SQL. You can likely find much better examples and help if you search in a forum specific to your database type or stackoverflow.com, since it's not specific to Metabase.

Thanks for the lead.

I found the solution without using the CTE.

SELECT (
    SELECT sum(s.qty) FROM sdeliveryd AS s
    INNER JOIN sdelivery AS s1 on s.sdhnum = s1.sdhnum 
    WHERE s1.bpcord = "020050"
  ) - (
    SELECT sum(s.amtnot) FROM sinvoice AS s
    INNER JOIN sinvoicev AS s1 on s.num = s1.num
    WHERE s1.bpcinv = "020050"
)

much simpler