Create a gist now

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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…
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");
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment