SQL how to create a view with 2 sums of diferent tables, when those tables have diferent number of r-CodePudding

I have 3 tables, "accounts" "charges" and "payments" I want to create a view with the account data the sum of the charges, and the sum of the payments, but payments have usually more records than charges for the same account when I create the view and GROUP BY charges end up "duplicated" like by the number of rows mismatch. y tried using COALLECE but still repeat the charge by the number of payments. this is the query:

create view v_notas as select `n`.`id` AS `id`,`n`.`cliente` AS `cliente`,`n`.`fecha` AS `fecha`,`n`.`tel` AS `tel`,`n`.`dir` AS `dir`,`n`.`status` AS `status`,
sum(`c`.`subtotal`) AS `monto`,
sum(`a`.`monto`) AS `abonos` 
from ((`mvcdb`.`nota` `n` 
left join `mvcdb`.`concepto` `c` on((`n`.`id` = `c`.`nota_id`))) 
left join `mvcdb`.`abono` `a` on((`n`.`id` = `a`.`nota_id`))) 
group by `n`.`id`

also tried with coallece

...coalesce(sum(`c`.`subtotal`),0) AS `monto`,
coalesce(sum(`a`.`monto`),0) AS `abonos`...

hope I was concise (enough) I tried StackOverflow in my native language but is deserted

CodePudding user response：

You can use two sub-selects:

create view v_notas as 
select 
   `n`.`id` AS `id`,
   `n`.`cliente` AS `cliente`,
   `n`.`fecha` AS `fecha`,
   `n`.`tel` AS `tel`,
   `n`.`dir` AS `dir`,
   `n`.`status` AS `status`,
   (select sum(subtotal) from `mvcdb`.`concepto` c on c.nota_id = n.id) as monto,
   (select sum(`mbcdb`.abono`) from `mvcdb`.`nota` a on a.nota_id = n.id) AS abonos 
from `mvcdb`.`nota` `n` 
group by `n`.`id`