SQL query with 3 tables, summation in 2 and aggregation

Asked

Viewed 32 times

1

Hello.

I’m using the Firebird 2.5 database and I have 3 tables: Customers (client/patient registration); Consultation (consultations that customers have conducted); Payments (informs the amounts paid by customers).

This is the (basic) structure of the 3 tables:

----------------
| CLIENTES     |
|--------------|
| idCli | Nome |
----------------

-------------------------------------
| CONSULTAS C                       |
|-----------------------------------|
| idCons | idCli | Data | ValorCons |
-------------------------------------

---------------------------------
| PAGAMENTOS P                  |
|-------------------------------|
| idPg | idCli | Data | ValorPg |
---------------------------------

I need to make a query that returns another table with the following data:

  • customer name (CLIENTS table)
  • sum of the values of the consultations held (table CONSULTATIONS)
  • sum of the amounts paid by the customer (PAYMENTS table)
  • customer balance ((sum of query values) - (sum of amounts paid))

I’d like a way out:

------------------------------------------------------------------------------
| RESULTADO                                                                  |
|----------------------------------------------------------------------------|
| Nome | SUM(C.ValorCons) | SUM(P.ValorPg) | SUM(C.ValorCons)-SUM(P.ValorPg) |
------------------------------------------------------------------------------

I have tried several variations of the code below (unsuccessfully):

 SELECT consultas.idcli,
 SUM(consultas.ValorCons) as TotalCons,
 SUM(pagamentos.ValorPg) as TotalPg,
 SUM(consultas.ValorCons - pagamentos.ValorPg) as saldo,
 clients.nome
 FROM consultas
 LEFT JOIN clientes ON consultas.idcli=clientes.idcli
 LEFT JOIN pagamentos ON pagamentos.idcli=clientes.idcli
 GROUP BY consultas.idcli, clientes.nome

Complementing the response of Sérgio Louro, the result was:

SELECT
    clientes.nome,
    (SELECT SUM(ValorCons) FROM consultas WHERE idCli = clientes.idCli) AS total_consultas,
    (SELECT SUM(ValorPg) FROM pagamentos WHERE idCli = clientes.idCli) AS total_pagamentos
    ( (SELECT SUM(ValorCons) FROM consultas WHERE idCli = clientes.idCli) - 
      (SELECT SUM(ValorPg) FROM pagamentos WHERE idCli = clientes.idCli) ) as saldo
FROM clientes
sql

  • Are you sure it should be a left Join?

  • To tell the truth no... But I tried several ways (RIGHT, INNER, FULL OUTER). I believe that it is not this way (with two JOIN) that is done. I must be thinking wrong...

  • Check this https://answall.com/questions/523756/ajuda-com-select-somando-campos-accountings-pay-e-receiver-reports-mysql

1 answer

1


Hello, dnat!

You can do this using subconsultation. See the example below:

SELECT
    clientes.nome,
    (SELECT SUM(ValorCons) FROM consultas WHERE idCli = clientes.idCli) AS total_consultas,
    (SELECT SUM(ValorPg) FROM pagamentos WHERE idCli = clientes.idCli) AS total_pagamentos
FROM clientes

  • Hi Ségio, thank you so much. It worked out! But please help me make a difference between the two columns total consultations and total_payments. I tried to add below the second select: (SELECT SUM(consultas.ValorCons-pagamentos.ValorPg) FROM consultas,pagamentos WHERE consultas.idCli = clientes.idCli) AS saldo but it didn’t work.

  • Check it out. I think I got it: ( (SELECT SUM(ValorCons) FROM consultas WHERE idCli = clientes.idCli) - 
 (SELECT SUM(valorpago) FROM pagamentos WHERE idCli = clientes.idCli) ) as saldo. I just don’t know if it’s optimized. Thank you @Sérgio Louro.

  • http://sqlfiddle.com/#! 9/65b645/1

Browser other questions tagged sql

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