Get a custom field count for Woocommerce orders

Hi I am working on woocommerce

I have added custom fields in Woocommerce for orders which is an Agent name each order is assigned with an Agent.

I would like to get Agent name with its total order count.

I have created custom widget and its working well , just issue with the agent name its coming every time. I need agent name only one time and its total count.

Below is the code I have tried:

function wc_orders_dashboard_widget_function() {
    $args   = array(
            'meta_key'          =>'_wc_acof_2',
            'post_type'         => 'shop_order',
            'post_status'       => 'All',
            'posts_per_page'    => 5 
          );
    $orders = get_posts( $args );

    if( count( $orders ) > 0 ) {
        ?>      
        <table width="100%" class="vao_orders_table">
            <tr>
                <th><?php _e( 'Agent Name', 'woocommerce' ); ?></th>
                <th><?php _e( 'Order Count', 'woocommerce' ); ?></th>
            </tr>
        <?php       
        foreach ( $orders as $key => $value ) {

            ?>
            <tr>
                <td>
                <?php

                $order   =   new WC_Order( $value->ID );

                $order_id = $order->get_order_number();

                $agent_name = get_post_meta( $order_id, '_wc_acof_2', true );

                // 1. Get the order ID and its link
                if ( $agent_name ) {                
                    echo $agent_name;
                ?>
                </td>               
                <td>                

                <?php
                    // 2. Get status of the order
                    $order_count += wp_count_posts($value->post_type );
                    echo $order_count;
                }
                ?>
                </td>               

            </tr>
            <?php           
        }

        ?></table><?php

        // 4. Adding All orders link which will redirect to Orders page of WooCommerce
        printf( '<div id="vao_orders"><span class="dashicons dashicons-cart"></span> <a href="%s">' . __( 'View all orders' ) . '</a></div>', admin_url( 'edit.php?post_type=shop_order' ) );       
    }else{
        // If no orders then display No Orders message
        echo __( 'No Orders.' );
    }
}

But it displays output like below :

Agent Name Order Count
Agent1 1
Agent1 2
Agent2 3
Agent1 4

My expected output :

Agent Name Order Count
Agent1 3
Agent2 1

Can you guys please help me on this ? What I am missing ?

Answer

This can be done with a very light and simple SQL query, that will compact your function:

function wc_orders_dashboard_widget_function() {
    global $wpdb;
    $domain = 'woocommerce';

    // The SQL query
    $results = $wpdb->get_results( "
        SELECT meta_value as value, count(meta_value) as count
        FROM {$wpdb->prefix}postmeta
        WHERE meta_key LIKE '_wc_acof_2'
        GROUP BY meta_value
        ORDER BY meta_value
    " );

    if( count( $results ) > 0 ):
        ?>      
        <table width="100%" class="vao_orders_table">
            <tr>
                <th><?php _e( 'Agent Name', $domain ); ?></th>
                <th><?php _e( 'Order Count', $domain ); ?></th>
            </tr>
        <?php foreach ( $results as $result ): ?>
            <tr>
                <td><?php echo $result->value; ?></td>               
                <td><?php echo $result->count; ?></td>
            </tr>
        <?php endforeach; ?>
        </table>
        <?php

        // 4. Adding All orders link which will redirect to Orders page of WooCommerce
        printf( '<div id="vao_orders">
            <span class="dashicons dashicons-cart"></span> <a href="%s">%s</a>
        </div>', admin_url( 'edit.php?post_type=shop_order' ), __( 'View all orders', $domain ) );       
    else:
        // If no orders then display No Orders message
        echo __( 'No Orders.', $domain );
    endif;
}

Code goes in function.php file of your active child theme (or active theme).

Tested and works.