Merge record of one table with SUM of another for each record

Question

Merge record of one table with SUM of another for each record

Navigation

#1 by (1 votes)
#2 by (0 votes)

1

As part of my database I have 2 tables (clients and loans), of which I am interested in operating with the following fields:

LOANS: (Customer ID, Loan Amount)
CLIENTS: (Customer ID)

Consider that a client can have several loans.

What I should do, is to generate a view that shows all the clients (their personal information) and at the end of the registration the sum of the amounts of their loans. Let me explain with an example ...

If in USERS I have Juan with id = 1 and in LOANS I have registered that the client with id = 1 requested 3 loans of $ 100, $ 200 and $ 300, I must show the following record:

ID: 1 - Name: Juan - (Other information fields) - Debt: $ 600

And so for all customers , if a customer has not asked for loans, I show their registration with a "$ 0" at the end.

I have the restriction that I should not create the Debt field in the CLIENTS table (which I understand because it would generate redundancy).

My problem is that I do not know how to assign the sum of LOAN amounts to its respective user in CLIENTS

Can someone help me with the consultation?

mysql sql

asked by Christopher Bryan 08.09.2017 в 05:23

source

2 answers

0

Just to complement the answer you already have, there are other ways:

Using subquery as a field:

select c.id,
       c.nombre,
       (select SUM(Monto) from prestamos where cliente_id = c.id) as 'TotalPrestamos'
       from clientes c

Using `LEFT JOINS` and `GROUP BY`

select c.id,
       c.nombre,
       SUM(p.monto) AS 'TotalPrestamos'
       FROM clientes c
       LEFT JOIN prestamos p
          on c.id = p.cliente_id
       GROUP BY c.id,
                c.nombre

Using `LEFT JOINS` , subquery at the table level and `GROUP BY`

select c.id,
       c.nombre,
       p.TotalPrestamos
       FROM clientes c
       LEFT JOIN (SELECT cliente_id, SUM(Monto) as 'TotalPrestamos' 
                         FROM prestamos GROUP BY cliente_id
          ) p
          on c.id = p.cliente_id

In general lines, it is preferable to use the last two consultations on the first, they tend to be more "performantes".

answered by 08.09.2017 в 17:16

Symfony Doctrine: Unable to create database Error with phpmailer form Deprecated: Function split

score 1 · Accepted Answer

I've done an example by creating a function to get a client's total debt.

Get Debt function:

CREATE FUNCTION 'obtenerDeuda'(id INT) 
RETURNS int(11)
BEGIN
RETURN (SELECT SUM(monto) from Prestamos where idCliente = id);
END

The function returns the sum of the column amount of the records with the customer id.

Use of the function in a query:

SELECT
Clientes.nombre,Clientes.apellidos,Clientes.telefono,obtenerDeuda(Clientes.idCliente) as deuda
FROM
Clientes

Merge record of one table with SUM of another for each record

2 answers

Using subquery as a field:

Using LEFT JOINS and GROUP BY

Using LEFT JOINS , subquery at the table level and GROUP BY

Using `LEFT JOINS` and `GROUP BY`

Using `LEFT JOINS` , subquery at the table level and `GROUP BY`