wordpress - Calculating VAT / tax on a total in MySQL - with VAT rate depending on a location field -

i'm trying create report in mysql (from tables in wordpress/woocommerce installation, way shop tax different way woocommerce things, can't use woocommerce reports.)

the calculation needs work out tax rate of 20% (uk vat) total - if customer in particular location. so, if customer in 'world' tax rate 0%. if customer in uk, tax rate 20%. total charged remains same (so more profit made exports local sales!)

i started :

select trim(leading 'order –' post_title) date, m1.meta_value _order_number, m2.meta_value _location, m3.meta_value _order_total wp_posts  left join wp_postmeta m1 on m1.post_id = wp_posts.id , m1.meta_key = '_order_number' left join wp_postmeta m2 on m2.post_id = wp_posts.id , m2.meta_key = '_shipping_method_title' left join wp_postmeta m3 on m3.post_id = wp_posts.id , m3.meta_key = '_order_total' wp_posts.post_type = 'shop_order' 

that gave me table this:

date     |  order no  | location    |  order total  may 2013 |  123       |world         |1124.00  jan 2013 |  124       |uk            |163.00 

so far good. tried add sums work out amount of vat.

select trim(leading 'order –' post_title) date, m1.meta_value _order_number, m2.meta_value _location, m3.meta_value _order_total, sum(m3.meta_value*20/120) _vat, sum(m3.meta_value*100/120) _net wp_posts  left join wp_postmeta m1 on m1.post_id = wp_posts.id , m1.meta_key = '_order_number' left join wp_postmeta m2 on m2.post_id = wp_posts.id , m2.meta_key = '_shipping_method_title' left join wp_postmeta m3 on m3.post_id = wp_posts.id , m3.meta_key = '_order_total' wp_posts.post_type = 'shop_order' 

i thought give me:

date       |     order no | location  |    order total | vat  |   net  may 2013  |             123|   world |    1124.00  |    187.33 |   1311.33  jan 2013  |         124  |    uk     |      163.00   |    27.17 |  190.17 

and go on try work out how change rate location. unfortunately, seems sum everything, rather giving me results row row.

1) have screwed sums together?

2) ideas on how best apply vat calculation when _shipping_method_title=uk?

3) clearly, works till tax rates change. best bet dealing limit report date @ point when tax rate next amended, , make new report future orders new tax rates apply? or there cleverer way?

i appreciate charging flat rate customer , paying variable tax odd approach don't change that, tasked providing report. :-(

edit gordon, have sorted out sum problem, , query looks , produces table layout expected, shown above:

select trim(leading 'order –' post_title) date, m1.meta_value _order_number, m2.meta_value _location, m3.meta_value _order_total, sum(m3.meta_value*20/120) _vat, sum(m3.meta_value*100/120) _net wp_posts  left join wp_postmeta m1 on m1.post_id = wp_posts.id , m1.meta_key = '_order_number' left join wp_postmeta m2 on m2.post_id = wp_posts.id , m2.meta_key = '_shipping_method_title' left join wp_postmeta m3 on m3.post_id = wp_posts.id , m3.meta_key = '_order_total' wp_posts.post_type = 'shop_order' group wp_posts.id 

but i'm still not sure how change multipliers based on value in location field. possible, or barking wrong tree here?

edit 2

i've worked out partial solution - instead of sum lines used case give me calculated value vat.

case          when m2.meta_value = 'world delivery' 0         when m2.meta_value = 'uk delivery'  (m3.meta_value*20/120)         when m2.meta_value = 'european delivery'  (m3.meta_value*20/120) end _vat     

unfortunately can't repeat case statement _net value (price - vat, it's same case vat, creates error.

i want like

 sum(_order_total-_vat) _net 

but *_order_total* , *_vat* not recognised columns can calculated from.

final edit again gordon, final query looks this, i'm adding in case else might find helpful. query works wordpress exports , reports wordpress plugin, can integrated wordpress admin , exported excel, nifty.

select trim(leading 'order –' post_title) date, m1.meta_value _order_number, m2.meta_value _customer_location, m3.meta_value _order_total,  round(sum(case when m2.meta_value = 'world delivery' 0 else m3.meta_value*20/120 end),2) _vat, round(sum(case when m2.meta_value = 'world delivery' m3.meta_value else m3.meta_value*100/120 end),2) _net wp_posts  left join wp_postmeta m1 on m1.post_id = wp_posts.id , m1.meta_key = '_order_number' left join wp_postmeta m2 on m2.post_id = wp_posts.id , m2.meta_key = '_shipping_method_title' left join wp_postmeta m3 on m3.post_id = wp_posts.id , m3.meta_key = '_order_total' wp_posts.post_type = 'shop_order' , wp_comments.comment_post_id = wp_posts.id  , wp_comments.comment_content="order status changed processing completed." group wp_posts.id 

the reason getting 1 row because sum() function makes aggregation query. but, don't have group by. think grouping postid fixes that, along other tweaks select:

select trim(leading 'order –' post_title) date, max(m1.meta_value) _order_number, max(m2.meta_value) _customer_location, max(m3.meta_value) _order_total, sum(m3.meta_value*20/120) _vat, sum(m3.meta_value*100/120) _net wp_posts  left join wp_postmeta m1 on m1.post_id = wp_posts.id , m1.meta_key = '_order_number' left join wp_postmeta m2 on m2.post_id = wp_posts.id , m2.meta_key = '_shipping_method_title' left join wp_postmeta m3 on m3.post_id = wp_posts.id , m3.meta_key = '_order_total' wp_posts.post_type = 'shop_order' 

this type of data rather hard used to. data single record split among multiple rows.

to change multiplier dependent on location, use where clause. here example:

sum(case when location = 'uk' 0 else m3.meta_value*20/120 end) _vat, sum(case when location = 'uk' m3.meta_value else m3.meta_value*100/120 end) _net