Perform query of totals between two tables without duplicating the values

Question

Perform query of totals between two tables without duplicating the values

Navigation

#1 by (0 votes)

0

I need to combine 2 tables, make a summary and get their differences. In the services table I have the cost of the service, in the payment table I have the amounts and means of payment associated, as I can make a summary in such a way that independent of the amount of means of payment does not double the value of the service. p>

I'm in the design stage, so I can also change the design of the tables if the solution warrants it.

Table1_Services

 ID_Serv    Servicio     Monto  
 1          59927840701  118.441,50

Table2_Payments

 ID_Pago    Pago     Monto          ID_Serv
 3          Debito   39.480,50      1
 4          Credito  78.961,00      1

Query_Totals

 Total_Servicios    Total_Pagos     Diferencia  
 236.883,00         118.441,50      -118.441,50

** Error in Total_Service , is duplicated by having the same associated index twice

mysql sql

asked by Daniel Gonzalez 05.03.2018 в 20:55

source

1 answer

objectDb - explorer.jar load a BD Put single quotes to store in mysql

score 0 · Answer 1

good day. If I'm not mistaken, you mean the use of group by, check out this link link , anyway I made an example I hope I have understood the question well, I would ask for more information but I can not comment on your question for not having the necessary reputation yet.

declare @servicios as table(
    id_serv  int PRIMARY KEY IDENTITY(1, 1),
    servicio bigint not null,
    monto decimal(16,4) 
);

declare @pagos as table(
    id_pago int PRIMARY KEY IDENTITY(1, 1),
    pago varchar(100),
    monto decimal(16, 4),
    id_serv int 
);

insert into @servicios(servicio, monto) values(59927840701, 118441.50 ),  
    (59927840702, 118441.50),(59927840703, 543  )
insert into @pagos(pago, monto, id_serv) values('Debito', 39480.50, 1), 
    ('Credito', 78961.00, 1),('Debito', 0, 2), ('Credito',59220.7500 , 2)


select 
    sum(pag.monto) as total_pagos,
    max(ser.monto) as total_servicios,
    max(ser.monto) - sum(pag.monto) as diferencia,
    max(ser.servicio) as servicio
from @pagos  pag 
inner join @servicios as ser  on ser.id_serv = pag.id_serv
group by ser.id_serv