MySQL WordPress sanitized Query for SUM, AVG of Downloads by Download Type. NOTES: Additional Types (ie HTML or Source Code) can be added. Query qepends on Download Monitor and Posts2Posts plugin for other WP users' ease of adding files and assigning them to posts. Grouping won't work here so I use CASE. Future plans: 1) Remove excess JOINS on t…

WP-Query-Download-Type-SUM-AVG-Day-Month

global $wpdb;
$result = $wpdb->get_results("
SELECT  p.post_title AS 'Title',
  SUM(CASE WHEN t.name = 'PDF' THEN m.meta_value ELSE NULL END) as 'PDF',
   SUM(CASE WHEN t.name = 'EPUB' THEN m.meta_value ELSE NULL END) as 'EPUB',
  SUM(m.meta_value) AS 'Totals', 
  round(AVG((m.meta_value)/180)) as 'DailyAvg',
  round(AVG((m.meta_value)/30)) as 'MonthlyAvg',
  r.p2p_from AS 'From'
FROM 
$wpdb->posts as p 
  INNER JOIN $wpdb->postmeta as m
  ON p.ID = m.post_id
  INNER JOIN $wpdb->term_relationships as o
  ON o.object_id = m.post_id
INNER JOIN $wpdb->term_taxonomy as x
  ON x.term_taxonomy_id = o.term_taxonomy_id
INNER JOIN $wpdb->terms as t
  ON t.term_id = x.term_id
INNER JOIN {$wpdb->prefix}p2p AS r ON m.post_id = r.p2p_to
WHERE  m.meta_key =  '_download_count' AND p.post_type='dlm_download'
  GROUP BY r.p2p_from
   ORDER BY p.post_title ASC");