Total price value per product category - Woocommerce

On a Wordpress page, I want to show all product categories followed by the total value of the products in that category, followed by a row with totals. For example:

Category	# products	total value
Stamps	100	2099,-
Coins	400	2399,-
Cards	50	399,-
TOTAL	550	4897,-

So basically, I need to make a loop for every category and then add up every regular_price of those products. Finally, I need to addup all those prices to get the total.

Pseudo code / (not working):

$categories = get_categories();
foreach($categories as $category) {
   echo <a href="' . get_category_link($category->term_id) . '">' . $category->name . '</a>>';
}
$totals_per_category = array();
 foreach($categories as $key => $category) {
 foreach($category as $id => $value) {
                $totals_per_category[$id] += $value;
            }
}
Echo $value;

Thanks for any advice how to make this work.

Solution

To get all categories, you can use the get_terms function like this:

    $categories = get_terms(
        array(
            'taxonomy'   => 'product_cat',
            'orderby'    => 'name',
            'hide_empty' => false,
        )
    );

Feel free to modify the arguments as per your needs. Once you have an array of terms, you can use it to get the term id of each category and use the latter to run an SQL query that will allow you to get the total value of all products in that category.

It can be done like this:

    $totals_per_category = array();
    foreach ($categories as $key => $category) {
        $term_id = $category->term_id;
        global $wpdb;
        $total = $wpdb->get_var(
            "
            SELECT sum(meta_value)
            FROM $wpdb->postmeta
            INNER JOIN {$wpdb->term_relationships} ON ( {$wpdb->term_relationships}.object_id = {$wpdb->postmeta}.post_id )
            WHERE ( {$wpdb->term_relationships}.term_taxonomy_id IN ($term_id) )
            AND {$wpdb->postmeta}.meta_key = '_regular_price'"
        );
        $totals_per_category[$term_id] = $total;
    }

Now since we have the total products value of each category, we can get the total of all categories using array_sum.

echo array_sum($totals_per_category); // This will output the total of all categories

Now you can use the logic above to retrieve totals and build the markup around it as you like (table, div ...etc).