Generate a table of orders grouped by user_id which sums quantity of product

So I have a large mysql table (in woocommerce) where people can purchase items more than once. What I would like to do is have a table dynamically generate where I select the product I want to query for (from a dropdown menu in php if possible) and group the quantity of the product purchased by user_id. I found this code previously (http://codecharismatic.com/sql-script-to-get-all-woocommerce-orders-including-metadata/) but have been unable to at least generate the table I would like to get…

select
    p.ID as order_id,
    p.post_date,
    max( CASE WHEN pm.meta_key = '_billing_first_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_first_name,
    max( CASE WHEN pm.meta_key = '_billing_last_name' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_last_name,
    max( CASE WHEN pm.meta_key = '_billing_address_1' and p.ID = pm.post_id THEN pm.meta_value END ) as _billing_address_1,
    ( select group_concat( order_item_name separator '|' ) from wp_woocommerce_order_items where order_id = p.ID ) as order_items
from
    wp_posts p 
    join wp_postmeta pm on p.ID = pm.post_id
    join wp_woocommerce_order_items oi on p.ID = oi.order_id
where
    post_type = 'shop_order' and
    post_status = 'wc-completed' and
    oi.order_item_name = 'Product Name'
group by
    user_id

my ultimate goal would be this with a dropdown menu to select product in question based on product ID so I can see how much each user has purchased of the product queried (completed orders only as per code above);

user ID | Product | quantity | billing address 
23 | chair | 4 | 22 Bank street
42 | chair | 12 | 123 Smith Road
88 | chair | 5 | 3 Parker avenue

etc

Advertisement