0

I want to calculate the average fuel consumption for every car in my table. I have a spendforfuel table with IdCar, Odometer, Quantity fields. Odometer field is current mileage. Quantity are the litres consumed between the current mileage and last detected mileage.

Here is my formula: 

100 / ( (MAX(Odometer) - MIN(Odometer)) / (SUM(Quantity) - FIRST(Quantity) ) )

Here is what I did: 

$q = $this->db->select()
                ->from(array('s1' => 'spendforfuel'), array('fuel_consumption' => '100 / ( s2.odometer_sum / ( s3.quantity_sum - s4.first_quantity ) )'))
                ->joinLeft(array('s2' => 'spendforfuel'), 's2.IdCar = s1.IdCar', array('odometer_sum' => 'MAX(s2.Odometer) - MIN(s2.Odometer)'))
                ->joinLeft(array('s3' => 'spendforfuel'), 's3.IdCar = s1.IdCar', array('quantity_sum' => 'SUM(s3.Quantity)'))
                ->joinLeft(array('s4' => 'spendforfuel'), 's4.IdCar = s1.IdCar', array('first_quantity' => 's4.Quantity'))
                ->where('s4.Odometer > ?', 0)
                ->limit('1 ASC')
                ->group('s1.CarId')
                ->where('s1.CarId = ?', 76);

I am not sure if I have to use joins at all. Any ideas?

enenen
  • 1,967
  • 2
  • 17
  • 33
  • Why try to do this in sql at all? It seems expensive to do this many calculations in sql. – RockyFord Aug 29 '12 at 04:18
  • So, it is better idea to get all parts from the formula separately and then calculate the average with PHP? – enenen Aug 29 '12 at 05:51
  • Not sure if it's a better idea but it sure seems simpler, and I'm all about simple. Then again, a lot depends on the structure of your application as to whether it makes sense to do the calculations in sql or in php. Also the fact that I suck at SQL may be coloring my point of view. – RockyFord Aug 30 '12 at 08:18

0 Answers0