Query in two tables with Count

Asked

Viewed 1,222 times

0

I have a table in mysql that has a number of contracted items.

Table itens_contratados

id_hired
Item
Qty contract

Table servico, in this I inform the service I performed in the contract

id_hired

I want to make a query that informs me so:

itens_contratados.id_contratado itens_contratados.qtd_contratada servicos.qtd_servico saldo

Where qtd_servico is equal to the amount of records found in the table servico concerning the id_contratado and saldo = qtd_contratada - qtd_servico

mysql sql

  • It would be interesting to put the structure of your tables and some data in this link: https://www.google.com/url?sa=t&rct=j&q=&esrc=s&source=web&cd=1&cad=rja&uact=8&sqi=2&ved=0ahUKEwif-r3r34bVAhXJDpAKHeI1ApoQFggjMAA&url=http%3A%2F%2Fsqlfiddle.com%2F&usg=Afqjcnfm12txauoyr1fcpsh3njbga9q20a makes it easy for us to test the query before putting as an answer

  • ok buddy, but I can’t put the table structure. I want something generic.... this data is not my table. I want to understand the logic

  • Okay, I’ll do something generic

  • Thank you! for the help!

1 answer

1


As you asked in the comments of your question, below is a query generic using INNER JOIN:

SELECT a.id_contratado, a.Qtde_contratada, COUNT(b.id_contratado) qtde_servico, (a.Qtde_contratada - COUNT(b.id_contratado)) saldo
FROM items_contratados a
INNER JOIN servico b ON a.id_contratado = b.id_contratado
GROUP BY a.id_contratado

  • Thank you very much, actually qtde_servico, is not a field but would be the amount of records in the service table, which correspond to the id_hired... balance is a.Qtde_hired - qtde_servico(Number of records in the service table).

  • I changed the query as your need.

  • fazendo as devidas mudancas preciso de algo assim SELECT a.id_pn, a.qtd, COUNT(b.id_pn) as qtde_servico, (COUNT(b.id_pn) - qtd_servico) saldo
FROM itens_contratados a
INNER JOIN controle_contratos b ON a.id_pn = b.id_pn
GROUP BY a.id_pn only gives error when subtracting by qtd_servico is not a field

  • Oops, I put the COUNT in the wrong position, I just fixed

  • Roberto Fagundes, your help was of great value, but my problem is much bigger than this. We can talk by email?

  • I’ll open up another help and be more specific!

  • [email protected]

Show 2 more comments

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.