valter.vx valter.vx - 11 days ago 5
SQL Question

SQL - Max and Min group by 2 collumns

I have the following table that shows me evey time a car has his tank filled. It returns the date, the car id, the mileage it had at that time and the liters filled:

| Date | Vehicle_ID | Mileage | Liters |
| 2016-10-20 | 234 | 123456 | 100 |
| 2016-10-20 | 345 | 458456 | 215 |
| 2016-10-20 | 323 | 756456 | 265 |
| 2016-10-25 | 234 | 123800 | 32 |
| 2016-10-26 | 345 | 459000 | 15 |
| 2016-10-26 | 323 | 756796 | 46 |


The idea is to calculate the average comsumption by month (I can't do it by day because not every car fills the tank every day).

To get that, i tried to get max(mileage)-min(mileage)/sum(liters) group by month. But this will only work for 1 specific car and 1 specific month.

If I try for 1 specific car and several months, the max and min will not return properly. If I add all the cars, even worse, as it will assume the max and min as if every car was the same.

select convert(char(7), Date, 127) as year_month,
sum("Liters tanked")/(max("Mileage")-min("Mileage"))*100 as Litres_per_100KM
from Tanking
where convert(varchar(10),"Date",23) >= DATEADD(mm, -5, GETDATE())
group by convert(char(7), Date, 127)


This will not work as it will assume the max and min from all the cars.

The "workflow" shoud be this:
- For each month, get the max and min mileage for each car. Calculate max-min to get the mileage it rode that month. Sum the mileage for each car to get a total mileage drivem by all the cars. Sum the liters tanked. Divide the total liters by the total mileage.

How can I get the result:

| YearMonth | Average |
| 2016-06 | 30 |
| 2016-07 | 32 |
| 2016-08 | 46 |
| 2016-09 | 34 |


Thanks

Answer

This is a more complicated problem than it seems. The problem is that you don't want to lose miles between months. It is tempting to do something like this:

select year(date), month(date),
       sum(liters) / (max(mileage) - min(mileage))
from Tanking
where Date >= dateadd(month, -5, getdate()) 
group by year(date), month(date);

However, this misses miles and liters that span month boundaries. In addition, the liters on the first record of the month are for the previous milage difference. Oops! That is not correct.

One way to fix this is to look up the next values. The query looks something like this:

select year(date), month(date),
       sum(next_liters) / (max(next_mileage) - min(mileage))
from (select t.*,
             lead(date) over (partition by vehicle_id order by date) as next_date,
             lead(mileage) over (partition by vehicle_id order by date) as next_mileage,
             lead(liters) over (partition by vehicle_id order by date) as next_liters
      from Tanking t
     ) t
where Date >= dateadd(month, -5, getdate()) 
group by year(date), month(date);

These queries use simplified column names, so escape characters don't interfere with the logic.

EDIT:

Oh, you have multiple cars (probably what vehicle_Id is there for). You want two levels of aggregation. The first query would look like:

select yyyy, mm, sum(liters) as liters, sum(mileage_diff) as mileage_diff,
       sum(mileage_diff) / sum(liters) as mileage_per_liter
from (select vehicle_id, year(date) as yyyy, month(date) as mm,
             sum(liters) as liters,
             (max(mileage) - min(mileage)) as mileage_diff
      from Tanking
      where Date >= dateadd(month, -5, getdate()) 
      group by vehicle_year(date), month(date)
     ) t
group by yyyy, mm;

Similar changes to the second query (with vehicle_id in the partition by clauses) would work for the second version.