You would probably be best of writing your own SQL query, for performance-reasons. Another option is to store the mileage separately in a custom field for each user. As for writing your own SQL query: you could query all users, then join their posts, then join the postmeta-table.
After that, you can use the SQL SUM
-function to take the sum of miles-fields in the postmeta-table, per user.
This would result in a query like this (using $wpdb
):
global $wpdb;
$results = $wpdb->get_results( "
SELECT
us.ID,
us.user_login,
SUM( pm.meta_value ) AS mileage
FROM
{$wpdb->users} us
JOIN
{$wpdb->posts} po
ON
us.ID = po.post_author
JOIN
{$wpdb->postmeta} pm
ON
po.ID = pm.post_id
WHERE
pm.meta_key = 'miles'
AND
po.post_status="publish"
GROUP BY
us.ID
" );
We start be selecting from the users-table. After this, we JOIN
all posts. By using GROUP BY
and joining the posts and postmeta after that, we retrieve all miles-postmeta-entries per user. This allows us to use SUM( pm.meta_value )
in selecting the columns, so we can get the total mileage per user.