How do I do subtraction and display the result in MYSQL query

Question

How do I do subtraction and display the result in MYSQL query

Navigation

#1 by (0 votes)

0

I have 3 Tables

Vehicles

idVeiculo | Placa | Prefixo

oleomotor

idMotor | idVeiculo | datatroca | kmtroca | horimetroca | proximatroca |idMecanico

I have this query below that shows the result of the last changes of each vehicle without repeating the vehicle. how do I include in this query the hodomentro table that will take the last hour of each vehicle and subtract with the proximatroca and give the result of how many hours is missing.

SELECT DISTINCT b.prefixo as idVeiculo, max(datatroca) as datatroca, max(kmtroca) as kmtroca, max(horimetroca) as horimetroca, max(proximatroca) as proximatroca, idMecanico FROM oleomotor a
                  JOIN veiculos b on (a.idVeiculo=b.idVeiculo)
                  GROUP BY a.idVeiculo order by datatroca DESC;

Hodometer

idVeiculo | km | horimetro

php mysql sql

asked by anonymous 01.05.2018 / 17:57

1 answer

Error with the django-blog-zinnia users model CodeIgniter - A function for several controllers

score 0 · Answer 1

You can use the TIMEDIFF() function that has the purpose returns the difference between two time values (expressed as a time value).

Add: timediff(proximatroca, max(horimetro)) as Horas Restantes

It would look like this:

SELECT DISTINCT b.prefixo as idVeiculo,
                max(datatroca) as datatroca,
                max(kmtroca) as kmtroca,
                max(horimetroca) as horimetroca,
                max(proximatroca) as proximatroca,
                idMecanico,
                timediff(proximatroca, max(horimetro)) as 'Horas Restantes'
FROM oleomotor a
JOIN veiculos b on (a.idVeiculo=b.idVeiculo) 
JOIN hodometro h on (a.idVeiculo= h.idVeiculo) 
GROUP BY a.idVeiculo 
ORDER BY datatroca DESC;