Average consumption per km

[silassanttos]

Hello good Morning!

I need some tips to lighten the mind ...
I'm new in the area and I can not glimpse this calculation ...
Use Oracle 11

I need to average consumption per km of the vehicles of a fleet, but I am kind of lost ...

First I did:

Select all

 
SELECT 
  CD_PLACA_PATRIMONIO PLACA 
  ,MAX(KM_ATUAL) MAIOR  
  ,MIN(KM_ATUAL) MENOR 
  ,(MAX(KM_ATUAL) - MIN(KM_ATUAL)) DIFERENCA 
FROM TB_CONTROLE_DESP_VEICULO  
WHERE  
  CD_PLACA_PATRIMONIO = 'NDE-2618' 
  AND FL_TIPO_SERVICO = 'a' 
  AND DT_LANCAMENTO BETWEEN :VDI AND :VDF 
GROUP BY  
  CD_PLACA_PATRIMONIO 

However, if the truck has more than two supply in the informed period the calculation will be stuck ...


Subsequently I did:

Select all

 
SELECT 
 CO.CD_PLACA_PATRIMONIO 
 ,CO.DT_LANCAMENTO 
 ,CO.KM_ATUAL 
 ,CO.QT_LITRO 
 ,CO.VL_SERVICO  
 ,AB.VL_UNITARIO  
FROM  
  TB_CONTROLE_DESP_VEICULO CO 
 ,TT_ABASTECIMENTO AB 
WHERE 
  CO.CD_CONTROLE_DESP = AB.CD_CONTROLE_DESP 
  AND CO.FL_TIPO_SERVICO = 'a' 
  AND CO.DT_LANCAMENTO BETWEEN FN_CONV_DATA ('01/01/2014') AND FN_CONV_DATA ('31/12/2014') 
  AND CO.CD_PLACA_PATRIMONIO LIKE 'NDE-2618' 
GROUP BY   
  CO.CD_PLACA_PATRIMONIO 
 ,CO.DT_LANCAMENTO 
 ,CO.KM_ATUAL 
 ,CO.QT_LITRO 
 ,CO.VL_SERVICO  
 ,AB.VL_UNITARIO 
  
ORDER BY 1, 2 DESC  

But in this case I can not do the calculations ...
I need to take the difference of mileage, the total baster,
do the average of the unit value of fuel, calculate the total stocked for AI Yes
Do the average consumption per km ...

It may be a very simple way, but as I said at the beginning I can glimpse anything ...
I'vê already researched the internet and nothing ...

If someone has done something similar, or can help me See a lot welcome!

[silassanttos]

Pox ...

No one!

[dr_gori]

So:
If you want to consider the price per liter then it is not just a simple average.
should use the weighted average [/b].

Here is an explanation: http://educacao.uol.com.br/disciplinas/ ... metica.htm

[silassanttos]

Thus:
If you want to consider the price per liter, then it is not just a simple average.
should use the weighted average [/b].

Here is an explanation: http://educacao.uol.com.br/disciplinas/ ... metica.htm

Maybe I was not clear by exposing my doubts, but anyway I managed to solve otherwise .. .
And even then, thank you ...: D