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?
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 ?
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;
@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.
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"
)