Group by unique pairs with no multiplicity I have a table AreaLocation with AreaID as the PKey and Location whereby there are many locations for each AreaID. Correspondingly there is another table Books with:
BookingID | DriverID | BLocation | Miles | Fare

I'm trying to get the average efficiency of each driver by area, i.e. I'd looking for a view with
| DriverID | AreaID | AVERAGE(Fare/Miles) |

where I'd like to group by distinct DriverID/AreaID however I'm not sure whether the following will work:
CREATE VIEW EFFICIENCY AS
 SELECT AreaID, Bookings.DriverID, AVERAGE(Bookings.Fare/Bookings.Miles) 
 FROM AreaLocations
 RIGHT JOIN Bookings ON
 ( AreaLocations.Location = Bookings.Location )
 GROUP BY DISTINCT (AreaID, Bookings.DriverID)

as I don't think distinct works on pairs.
With full_Group_on you can use for example this, if a &quot;_&quot; is not present  your columns
CREATE VIEW EFFICIENCY AS
SELECT SUBSTRING_INDEX(area_driver, '_', 1) as AreaID,SUBSTRING_INDEX(area_driver, '_', -1) AS DriverID, AvgPrice
 FROM (SELECT CONCAT(AreaID,&quot;_&quot;,Bookings.DriverID) area_driver, AVERAGE(Bookings.Fare/Bookings.Miles) AvgPrice
 FROM AreaLocations
 RIGHT JOIN Bookings ON
 ( AreaLocations.Location = Bookings.Location )
 GROUP BY  CONCAT(AreaID,&quot;_&quot;,Bookings.DriverID)) f1