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?


If you know the solution of this issue, please leave us a reply in Comment section, to update the question.

Wordpress related questions and answers: