2
I have 2 tables linked by id and need to add a field in table 2 grouping through a field in table 1
-----TABELA 1-----
ID_tab1 - tipo_pgto
-----TABELA 2-----
ID_tab2 - ID_tab1 - valor_pago
I want you to show me the sum of the value paid that is in table 2 however grouped by type_pgto that is in table 1
0
Try this:
select tipo_pgto, sum(valor_pago) from tab1 inner join
tab2 on tab1.ID_tab1 = tab2.ID_tab1_fk group by tipo_pgto
P s..: I added fk there only to make it easy to identify that it is a foreign key from table 1.
Basically a select with Inner Join, uniting the two tables, grouping and summing by type of payment.
See working on SQLFIDDLE (updated link)
References:
Browser other questions tagged select join inner
You are not signed in. Login or sign up in order to post.
Thank you very much for the answer, it has already helped a lot, but if I need the result of the sum of payment type 1 and also of payment type 2 in this consultation. What I want is to obtain the sum of the debit payments and the sum of the credit payments, and the payments are inserted in table 2 linked to the ID that this in table 1 and this table 1 is that has the field identifying whether it is debit or credit.
– Marcos A. Silva
Use
where tipo_pgto = tipodesejadoat the end of the code line.– user28595
Then I am not getting, I want that in the same query it already shows me the two results type: debit=1000,00 credit=1500,00 but inside the same WHILE
– Marcos A. Silva
@mcasite Guy the code already does this, see the link in sqlfiddle. You are listing the type of payment and the total of each.
– user28595
I did with the data everything in a single table and looked like this: SELECT id_lc, data_pgto, SUM(CASE WHEN type='2' THEN valor_pago ELSE 0 END) AS totalR, SUM(CASE WHEN type='1' THEN valor_pago ELSE 0 END) AS totalD FROM lc_motion WHERE ID='". $idEMP." ' AND data_pgto > 0 GROUP BY CONCAT(MONTH(data_pgto),YEAR(data_pgto)) ORDER BY STR_TO_DATE(CONCAT(MONTH(data_pgto), '-', YEAR(data_pgto)),'%m-%Y') ASC. This way inside WHILE I managed to get the total with TYPE 1 and 2 separate. But for reasons of adjustment in the programming I had to divide this table in two separating the field type
– Marcos A. Silva
@mcasite see the link now, if it’s the way you said.
– user28595
Resolvi assim: "SELECT lc_pagamentos.id_pg, lc_pagamentos.data_pgto, lc_parcelas.id_pa, lc_parcelas.id_fatura, lc_faturas.id_fa, SUM(CASE WHEN lc_faturas.tipo='2' THEN lc_pagamentos.valor_pago ELSE 0 END) AS totalR, SUM(CASE WHEN lc_faturas.tipo='1' THEN lc_pagamentos.valor_pago ELSE 0 END) AS totalD FROM lc_pagamentos, lc_parcelas, lc_faturas WHERE lc_pagamentos.ID='". $idEMP." ' AND lc_pagamentos.id_parcela = lc_parcelas.id_pa AND lc_parcelas.id_fatura = lc_faturas.id_fa AND lc_pagamentos.data_pgto > 0 GROUP BY CONCAT(MONTH(lc_pagamentos.data_pgto),YEAR(lc_pagamentos.data_pgto))". Mto Thanks
– Marcos A. Silva
@mcasite your problem is different from the question. But in fact the question was promptly answered. It would be interesting if you would mark as answered, so if someone with the question problem gets here, you will know that the answer is correct.
– user28595