Visualizer: Sample queries to generate charts

Easy Digital Downloads

Total revenue per payment gateway

SELECT pm1.meta_value, SUM(pm2.meta_value) FROM `wp_postmeta` pm1, `wp_postmeta` pm2 WHERE pm1.meta_key='_edd_payment_gateway' AND pm1.post_id=pm2.post_id AND pm2.meta_key='_edd_payment_total' AND pm1.meta_value<>'' GROUP BY pm1.meta_value

Number of subscriptions per product

SELECT prod.post_title, COUNT(*) FROM `wp_edd_subscriptions`, `wp_posts` prod, ( SELECT pm.post_id AS 'postid', pm.meta_value AS 'subsid' FROM `wp_posts` p, `wp_postmeta` pm WHERE p.post_type='edd_payment' AND p.id=pm.post_id AND pm.meta_key ='subscription_id' ) subs WHERE `wp_edd_subscriptions`.id = subs.subsid AND prod.id = product_id GROUP BY product_id ORDER BY prod.post_title

Total payments per subscribed product

SELECT prod.post_title, SUM((SELECT pp.meta_value FROM wp_postmeta pp WHERE pp.post_id=subs.postid AND pp.meta_key='_edd_payment_total')) FROM `wp_edd_subscriptions`, `wp_posts` prod, ( SELECT pm.post_id AS 'postid', pm.meta_value AS 'subsid' FROM `wp_posts` p, `wp_postmeta` pm WHERE p.post_type='edd_payment' AND p.id=pm.post_id AND pm.meta_key ='subscription_id' ) subs WHERE `wp_edd_subscriptions`.id = subs.subsid AND prod.id = product_id GROUP BY product_id ORDER BY prod.post_title

Payment per month/year

SELECT DATE_FORMAT(p.post_date, '%M, %Y') AS dt, CAST(SUM(pm1.meta_value) AS DECIMAL(10,2)) FROM `wp_postmeta` pm1, `wp_posts` p WHERE p.id=pm1.post_id AND pm1.meta_key='_edd_payment_total' GROUP BY dt, YEAR(p.post_date), MONTH(p.post_date) ORDER BY YEAR(p.post_date), MONTH(p.post_date)


WooCommerce

You can use the below resources to generate queries:


WordPress (core)

Comments per post

SELECT post_title, COUNT(DISTINCT c.`comment_ID`) AS cc FROM wp_posts p LEFT JOIN wp_comments c ON p.id = c.`comment_post_ID` GROUP BY p.ID ORDER BY cc DESC

Popularity of authors on the basis of comments per author

SELECT u.user_nicename, COUNT(DISTINCT c.`comment_ID`) AS cc FROM wp_users u LEFT JOIN wp_posts p ON p.`post_author` = u.id LEFT JOIN wp_comments c ON p.id = c.`comment_post_ID` GROUP BY u.id ORDER BY cc DESC



Can't find the query you want? Contact us  here and we might be able to help you!

Did this answer your question? Thanks for the feedback There was a problem submitting your feedback. Please try again later.