What i want to be returned is the top categories by sales(total_sales). For example if the category(taxonomy) ship has 64 sales, car has 45 sales & boat has 23 sales. Then what should be returned is ship, car & boat in that order. Here is my attempt at the query:
global $wpdb; $result = $wpdb->get_results ( "SELECT wp_term_taxonomy.taxonomy, sum(v1.meta_value) FROM wp_posts INNER JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) INNER JOIN wp_term_taxonomy ON (wp_term_relationships.term_taxonomy_id = wp_term_taxonomy.term_taxonomy_id) INNER JOIN wp_postmeta v1 ON (wp_posts.ID = v1.post_id) AND v1.meta_key='total_sales' WHERE wp_posts.post_status = 'publish' AND wp_posts.post_type = 'product' AND wp_term_taxonomy.taxonomy = 'product_cat' GROUP BY wp_term_taxonomy.taxonomy ORDER BY sum(v1.meta_value) DESC " );
Here is the query not in wordpress format, that works:
SELECT category, sum(sales) FROM table GROUP BY category ORDER BY sum(sales) DESC
How do i solve this? I only get 1 result in the array when it should return multiple results, where did i go wrong?
Read more here: Query only returning 1 result?