Sum of fields completed within the SELECT itself

Asked

Viewed 158 times

0

I have an SQL query in the following format: 

SELECT XXXX AS duracao, 
    calcula_horas_manutencao({data_inicio_f}, {data_final}, contrato.id) as horas_manutencao,
    calcula_horas_sistema({data_inicio_f},{data_final}, contrato.id) as horas_sistema, [...]

I want the result of duracao is the sum of horas_manutencao and of horas_sistema. Would it be possible for me to put the two together without having to repeat the function call?

Complete Query:

SELECT SUM(X.horas_manutencao + X.horas_sistema) AS duracao, meses meses, horas_manutencao horas_manutencao, horas_sistema horas_sistema,
Limite_horas Limite_horas, Nome Nome, 
cliente_id cliente_id, Valor Valor, valor_real valor_real, diferenca diferenca
FROM (
    SELECT contrato.id AS contrato,
           calcula_meses_atendimentos({data_inicio_f} , {data_final}) AS meses,
           calcula_horas_manutencao ({data_inicio_f},{data_final},contrato.id) AS horas_manutencao,
           calcula_horas_sistema({data_inicio_f},{data_final} ,contrato.id) AS horas_sistema,
           sec_to_time(time_to_sec(contrato.limite_horas) * calcula_meses_atendimentos({data_inicio_f} , {data_final})) AS Limite_horas,
           cliente.nome Nome,
           cliente.id cliente_id,
           format_money(contrato.valor) Valor,
           calcula_valor_real(contrato.id) AS valor_real,
           (calcula_valor_real(contrato.id) - contrato.valor) AS diferenca
    FROM sis_contratos contrato
    LEFT JOIN sis_atendimentos atendimento ON contrato.id = atendimento.contrato
    LEFT JOIN sis_clientes cliente ON contrato.cliente = cliente.id
    LEFT JOIN sis_modulos modulos ON contrato.id = modulos.id
    WHERE contrato.id <>24
      AND contrato.status='Ativo Mensal' [ AND atendimento.data_inicio BETWEEN {data_inicio_f} AND {data_final} ]
    GROUP BY contrato.id
    ORDER BY cliente.id,
         cliente.nome,
         atendimento.data_inicio DESC
) 
AS X
mysql query select

2 answers

0


You can make a SUBSELECT as follows:

SELECT SUM(X.horas_manutencao + X.horas_sistema) AS duracao FROM (
    SELECT calcula_horas_manutencao({data_inicio_f}, {data_final}, contrato.id) as horas_manutencao,
    calcula_horas_sistema({data_inicio_f},{data_final}, contrato.id) as horas_sistema, [...] 
) AS X

  • Assuming that the [...] is the rest of my SQL it would have to stay out of SUBSELECT right? I tried to do SELECT SUM(X.horas_manutencao + X.horas_sistema) AS duracao FROM (&#xA; SELECT calcula_horas_manutencao('2018-01-01', '2018-11-05', contrato.id) as horas_manutencao,&#xA; calcula_horas_sistema('2018-01-01','2018-11-05', contrato.id) as horas_sistema&#xA;) AS X, contrato.id AS contrato, [...] and returned syntax error pointing to the next line after this code.

  • No, you have to stay inside the subselect

  • This is exactly what I needed, only I had a while in the rest of the query, this seems to limit to a record right?

  • puts the whole query to see...

  • I put the full Query above, between { } is variable.

  • Where’s the while you quoted?

  • In vdd it is not limiting to a record, it is summing everything up, if you want to show more records, it must indicate which columns you want outside of subselect a at the end use a group by

Show 2 more comments

0

You must transform the fields to second or minute, timetosec or sectotime.

Then add and convert again to hour, minute, second format.

  • I did so to add friend sec_to_time(sum(time_to_sec(XXXX))), but my biggest problem is how to add the two functions together without having to run them again. (I need the values of the functions and also the sum of the two).

Browser other questions tagged mysql query select

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