SQL query to retrieve WooCommerce orders grouped by state

0 Views

what I’m trying to retrieve from the WordPress/WooCommerce database is a list of orders grouped by the states in the US with the Order totals and Order taxes summed for each state.

So the closest I’ve gotten is this query I’ve written:

Read More
SELECT meta_key, meta_value 
FROM wp_posts 
LEFT OUTER JOIN wp_postmeta ON wp_posts.ID=wp_postmeta.post_id 
WHERE post_type = "shop_order" 
AND (
meta_key = "_billing_state" 
OR meta_key = "_order_tax" 
OR meta_key = "_order_total"
) 
ORDER BY ID ASC

And it returns: 

meta_key         meta_value
_billing_state   NJ
_order_tax       0.21
_order_total     3.21
_billing_state   NJ
_order_tax       1.75
_order_total     26.70
_billing_state   IA
_order_tax       6.79
_order_total     103.54
_billing_state   PA
_order_tax       1.82
_order_total     27.72

…and so on.

What I really need to return is: 

state  order_tax_sum  order_total_sum
NJ     1.96           29.91
IA     6.79           103.54
PA     1.82           27.72

All help is massively appreciated!

Post Views: 0

Related posts

Leave a Reply

1 comment

  1. try something like this:

    select
    state,
    sum(order_tax_sum),
    sum(order_total_sum)
from (
    select
        (select meta_value from wp_postmeta pm1 where p.ID = pm1.post_id and meta_key = "_billing_state") as state,
        (select meta_value from wp_postmeta pm2 where p.ID = pm2.post_id and meta_key   = "_order_tax") as order_tax_sum,
        (select meta_value from wp_postmeta pm3 where p.ID = pm3.post_id and meta_key   = "_order_total") as order_total_sum
    from
        wp_posts AS p
    WHERE post_type = "shop_order"
) A
group by A.state

    caveat – totally untested, but the basic idea (do that join in the select statement itself) should be sound