get two columns in MySql and multiply results

ok i have the following:

user id    meta
   2      _qty      3
   2      Weight    20
   1      _qty      1
   1      weight    30

I need to grab the user id column then multiply the qty by the weight giving me for user id 2= 60 and user id = 1 30……
I’ve tried the following but to no avail:

Read More
 $myrows = $wpdb->get_results( "SELECT meta_value, SUM(_qty*weight) AS product_id FROM {$wpdb->prefix}woocommerce_order_itemmeta GROUP BY ($query_select_order_items)" );

where ($query_select_order_items) is the user_id.
how do i go about this?

Actual php I’m currently running:

/**
 * Returns all the orders made by the user
 *
 * @param int $user_id
 * @param string $status (completed|processing|canceled|on-hold etc)
 * @return array of order ids
 */
function fused_get_all_user_orders($user_id,$status='completed'){
    if(!$user_id)
        return false;

    $orders=array();//order ids

    $args = array(
        'numberposts'     => -1,
        'meta_key'        => '_customer_user',
        'meta_value'      => $user_id,
        'post_type'       => 'shop_order',
        'post_status'     => 'publish',
       /* 'tax_query'=>array(
                array(
                    'taxonomy'  =>'shop_order_status',
                    'field'     => 'slug',
                    'terms'     =>$status
                    )
        )  */
    );

    $posts=get_posts($args);
    //get the post ids as order ids
    $orders=wp_list_pluck( $posts, 'ID' );

    return $orders; 
}
function fused_get_all_products_ordered_by_user($user_id=false){

 $orders=fused_get_all_user_orders($user_id);
 if(empty($orders))
   return false;

 $order_list='('.join(',', $orders).')';//let us make a list for query

 //so we have all the orders made by this user which was successfull

 //we need to find the products in these order and make sure they are downloadable

 // find all products in these order

 global $wpdb;
 $query_select_order_items="SELECT order_item_id as id FROM {$wpdb->prefix}woocommerce_order_items WHERE order_id IN {$order_list}";

 $query_select_product_ids="SELECT meta_value as product_id FROM {$wpdb->prefix}woocommerce_order_itemmeta WHERE meta_key=%s AND order_item_id IN ($query_select_order_items)";


 $products=$wpdb->get_col($wpdb->prepare($query_select_product_ids,'weight'));

 $qty = $wpdb->get_col($wpdb->prepare($query_select_product_ids,'_qty'));


 return $products;
}

Where currently that $products on the end returns a list of weight’s or if i set it to _qty it will return a list of the quantities ordered…
sqlfiddle

Update from strawberries answer:

"SELECT order_item_id, weight * quantity total FROM ( 
 SELECT order_item_id, 
   MAX(CASE WHEN meta_key = '_qty' THEN meta_value ELSE 0 END) quantity , 
   MAX(CASE WHEN meta_key = 'weight' THEN meta_value ELSE 0 END) weight 
   FROM wp_woocommerce_order_itemmeta GROUP BY order_item_id) 
   WHERE  order_item_id  IN ($query_select_order_items) x;"

Related posts

Leave a Reply

3 comments

  1. Here’s one way…

    DROP TABLE IF EXISTS my_table;
    
    CREATE TABLE my_table
    (entity INT NOT NULL
    ,attribute VARCHAR(12) NOT NULL
    ,value INT NOT NULL
    ,PRIMARY KEY(entity,attribute)
    );
    
    INSERT INTO my_table VALUES
    (2,'quantity',3),
    (2,'weight',20),
    (1,'quantity',1),
    (1,'weight',30);
    
    SELECT entity, weight * quantity total
      FROM 
         ( SELECT entity
                , MAX(CASE WHEN attribute = 'quantity' THEN value END) quantity
                , MAX(CASE WHEN attribute = 'weight' THEN value END) weight
             FROM my_table
            GROUP
               BY entity
         ) x;
    
    +--------+-------+
    | entity | total |
    +--------+-------+
    |      1 |    30 |
    |      2 |    60 |
    +--------+-------+  
    

    … or even just …

    SELECT entity 
         , ROUND(EXP(SUM(LOG(value)))) total 
      FROM my_table 
     GROUP 
        BY entity;
    +--------+-------+
    | entity | total |
    +--------+-------+
    |      1 |    30 |
    |      2 |    60 |
    +--------+-------+  
    

    Adapting the first query to your fiddle might look like this…

    SELECT order_item_id, weight * quantity total
      FROM 
         ( SELECT order_item_id
                , MAX(CASE WHEN meta_key = '_qty' THEN meta_value ELSE 0 END) quantity
                , MAX(CASE WHEN meta_key = 'weight' THEN meta_value ELSE 0 END) weight
             FROM wp_woocommerce_order_itemmeta
            GROUP
               BY order_item_id
         ) x;
    

    …and the second might look like this…

    SELECT order_item_id
         , ROUND(EXP(SUM(LOG(meta_value)))) total 
      FROM wp_woocommerce_order_itemmeta
     WHERE meta_key IN('_qty','weight')
     GROUP 
        BY order_item_id;
    

    … and adapting the first to your latest requirement…

    SELECT i.order_item_id, weight * quantity total
      FROM 
         ( SELECT order_item_id 
                , MAX(CASE WHEN meta_key = '_qty' THEN meta_value ELSE 0 END) quantity
                , MAX(CASE WHEN meta_key = 'weight' THEN meta_value ELSE 0 END) weight
             FROM wp_woocommerce_order_itemmeta
            GROUP
               BY order_item_id 
         ) x
      JOIN wp_woocommerce_order_items i
        ON i.order_item_id = x.order_item_id
     WHERE i.order_id IN(647,649,650); 
    
  2. SELECT A.user_id, 
           SUM(A.meta_value * B.meta_value) 
    FROM   t A INNER JOIN t B ON A.user_id = B.user_id 
    WHERE  A.meta = 'weight'
       AND B.meta = '_qty'
    GROUP BY 
           A.user_id
    

    You can try it in this fiddle