Sum of fields filled in within SELECT itself

Question

Sum of fields filled in within SELECT itself

Navigation

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

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 to be the sum of horas_manutencao and horas_sistema . Would it be possible for me to add the two 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

asked by anonymous 05.11.2018 / 18:57

2 answers

0

You should transform the fields to seconds or minutes, timetosec or sectotime.

Then do the sum and convert back to hour, minute, second format.

05.11.2018 / 19:53

Delphi Problems When Listing XML Sub-Node How to write specific XML tags in PHP to MySQL

score 0 · Accepted Answer

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