MySQL SUM () INNER JOIN AND LIMIT using FOREIGN KEY

Question

I have the following tables: Vehicles:

1. VID Primary key
2. Reg_no
3. Date
4. Initial_mileage

Mileage

1. MID primary key
2. Reg_no FOREIGN KEY
3. Date
4. Miles_covered
5. To_service
6. Total

What I want is to sum(Total) for each last row of foreign key in mileage table.

In this case a query to sum MID 7 and MID 19

What I've tried:

    SELECT SUM(Mileage.Total) Totals 
    FROM (
        SELECT * 
        FROM Mileage 
        INNER JOIN Vehicles on Mileage.Registration_No=Vehicles.Registration_No 
        ORDER BY Mileage.MID DESC Limit 1
    ) SUB 
    ORDER BY Mileage.MID ASC;

I receive error 1060

What am I not doing right and how do I do it?

Answer 1

You can filter with a subquery, then aggregate. Assuming that mid can be used as an ordering column:

select sum(m.total) sum_total
from mileage m
where m.mid = (
    select max(m1.mid) from mileage m1 where m1.registration_no = m.registration_no
)

This works on all versions of MySQL, and should be an efficient solution, provided that you have an index on (registration_no, mid).

Note that you don't need the vehicle table to get the result that you want.