There may not be a need for a custom query, but I’d recommend it rather than hitting the database:
– once for the WP_Query
– once more for each post entry
And then manually working out the math in PHP. Do it all at once with one MySQL statement.
select sum(PM.meta_value) from wp_postmeta PM
join wp_posts P on P.ID = PM.post_id
where P.post_type="page"
and PM.meta_key='test'
With your code above, replace the $r=...
line with this:
$r = $wpdb->get_results( $wpdb->prepare( "
SELECT SUM(pm.meta_value) FROM {$wpdb->postmeta} pm
LEFT JOIN {$wpdb->posts} p ON p.ID = pm.post_id
WHERE pm.meta_key = '%s'
AND p.post_status="%s"
AND p.post_type="%s"
", $key, $status, $type ) );
$r[0]
should be your total. 🙂