SUM AND COUNT in a query with left join in MYSQL

Question

SUM AND COUNT in a query with left join in MYSQL

Navigation

#1 by (1 votes)

0

I have a problem when generating a query in my DB, I am going to explain in an abbreviated form the structure of the tables I want to make this query:

 **tbl usuario**
 - id_usuario 
 - nombre

**tbl alquiler**
 - id_alquiler
 - importe_total
 - id_usuario

**tbl impresion**
 - id_impresion
 - monto
 - id_alquiler

Each of these tables have more attributes, but I do not mention them because I do not need them in my query, and so as not to make the question longer, basically a user makes one or many rentals, and each rental has a total cost , now each rental can have 0 or many impressions (since what I rent is a product that can be printed or not). my problem is that I need a query to return for each user, the sum of the total amount of the rents I make, how many and also the sum of the amount of the impressions of each rent if they had or not. So far I have the following query.

SELECT u.nombre, SUM(a.importe_total) as TotalImporte,SUM(i.monto) as TotalImpresion, COUNT(a.id_alquiler) as Cantidad 
FROM Alquiler a
INNER JOIN Usuario u 
ON a.Id_usuario = u.Id_usuario 
LEFT JOIN Impresion i 
ON a.id_alquiler = i.id_alquiler
GROUP BY u.nombre

The problem I've had is that in some cases, a rental has up to 3 impressions, which has affected the count and the value of the total amount, I do not know if I am grouping them correctly or that I need to change in my query ?

mysql sql

asked by max 02.10.2017 в 19:06

source

1 answer

Matrix with Java Script how to make a field input type password show the last 4 characters

score 1 · Accepted Answer

I understand that the problem you have could be resolved by a subquery on impresiones

SELECT  u.nombre, 
    SUM(a.importe_total)    as TotalImporte,
    IFNULL(SUM(i.monto),0)  as TotalImpresion, 
    COUNT(a.id_alquiler)    as Cantidad 
    FROM Alquiler a
    INNER JOIN Usuario u 
        ON a.Id_usuario = u.Id_usuario 
    LEFT JOIN ( SELECT id_alquiler,
            SUM(monto) AS MONTO
            FROM Impresiones
            GROUP BY id_alquiler
        ) I
        ON a.id_alquiler = i.id_alquiler
    GROUP BY u.nombre;

Since of the rents you only need the sum of the amounts, we simply group by id_alquiler , which assures us that we will have a single record per id_alquiler , the IFNULL is to obtain 0 in case of not counting with records.