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

Question

I'm trying to create a report in MySQL (from tables in a Wordpress/Woocommerce installation, but the way this shop does tax is different to the way Woocommerce does things, so I can't use the Woocommerce reports.)

The calculation needs to work out a tax rate of 20% (UK VAT) from the total - but only if the customer is in a particular location. So, if the customer is in 'world' the tax rate is 0%. If the customer is in UK, the tax rate is 20%. But the total charged remains the same (so more profit is made from exports than local sales!)

I started with :

SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Location,
m3.meta_value AS _order_total
FROM wp_posts 
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'

That gave me a table like this:

Date     |  Order no  | Location    |  Order Total

May 2013 |  123       |World         |1124.00

Jan 2013 |  124       |UK            |163.00

So far so good. So then I tried to add some SUMs to work out the amount of VAT.

SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Location,
m3.meta_value AS _order_total,
SUM(m3.meta_value*20/120) AS _VAT,
SUM(m3.meta_value*100/120) AS _NET
FROM wp_posts 
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'

I thought this would 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 then I could go on to try to work out how to change the rate by location. Unfortunately, it seems to SUM everything, rather than giving me results row by row.

1) what have I screwed up that it sums everything together?

2) any ideas on how best to apply the VAT calculation only when _shipping_method_title=UK?

3) Clearly, this only works till the tax rates change. Is my best bet for dealing with that to just limit this report by date at the point when the tax rate is next amended, and make a new report for future orders to which new tax rates apply? Or is there a cleverer way?

I appreciate that charging a flat rate to the customer and paying variable tax is a slightly odd approach but I don't get to change that, I am just tasked with providing the report. :-(

EDIT Thanks to Gordon, I have sorted out my SUM problem, and now my query looks like this and produces the table layout I expected, as shown above:

SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Location,
m3.meta_value AS _order_total,
SUM(m3.meta_value*20/120) AS _VAT,
SUM(m3.meta_value*100/120) AS _NET
FROM wp_posts 
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'
GROUP BY wp_posts.ID

But I'm still not sure how to change the multipliers based on the value in the location field. Is that possible, or am I barking up the wrong tree here?

EDIT 2

I've worked out a partial solution - instead of the SUM lines I used a CASE to give me a calculated value for VAT.

CASE 
        when m2.meta_value = 'World Delivery' then 0
        when m2.meta_value = 'UK Delivery' then  (m3.meta_value*20/120)
        when m2.meta_value = 'European Delivery' then  (m3.meta_value*20/120)
END AS _VAT    

Unfortunately I can't just repeat the CASE statement for the _NET value (price - VAT, as it's the same CASE as VAT, so creates an error.

I want to do something like

 SUM(_order_total-_VAT) AS _NET

But *_order_total* and *_VAT* are not recognised as columns that can be calculated from.

FINAL EDIT Thanks again to Gordon, my final query looks like this, I'm just adding it in case someone else might find it helpful. This query works with the Wordpress Exports and Reports wordpress plugin, so can quickly be integrated into the Wordpress admin and exported to Excel, which is nifty.

SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
m1.meta_value AS _order_number,
m2.meta_value AS _Customer_Location,
m3.meta_value AS _order_total,

ROUND(SUM(case when m2.meta_value = 'World Delivery' then 0 else m3.meta_value*20/120 end),2) AS _VAT,
ROUND(SUM(case when m2.meta_value = 'World Delivery' then m3.meta_value else m3.meta_value*100/120 end),2) AS _NET
FROM wp_posts 
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order' AND wp_comments.comment_post_id = wp_posts.ID  AND wp_comments.comment_content="Order status changed from processing to completed."
GROUP BY wp_posts.ID

Answer 1

The reason you are getting just one row is because the sum() function makes this an aggregation query. But, you don't have a group by. I think grouping by the postid fixes that, along with some other tweaks to the select:

SELECT TRIM(LEADING 'Order –' FROM post_title) AS Date,
max(m1.meta_value) AS _order_number,
max(m2.meta_value) AS _Customer_Location,
max(m3.meta_value) AS _order_total,
SUM(m3.meta_value*20/120) AS _VAT,
SUM(m3.meta_value*100/120) AS _NET
FROM wp_posts 
LEFT JOIN wp_postmeta AS m1 ON m1.post_id = wp_posts.ID AND m1.meta_key = '_order_number'
LEFT JOIN wp_postmeta AS m2 ON m2.post_id = wp_posts.ID AND m2.meta_key = '_shipping_method_title'
LEFT JOIN wp_postmeta AS m3 ON m3.post_id = wp_posts.ID AND m3.meta_key = '_order_total'
WHERE wp_posts.post_type = 'shop_order'

This type of data is rather hard to get used to. All the data for a single record is split among multiple rows.

To change the multiplier dependent on location, use a where clause. Here is an example:

SUM(case when location = 'UK' then 0 else m3.meta_value*20/120 end) AS _VAT,
SUM(case when location = 'UK' then m3.meta_value else m3.meta_value*100/120 end) AS _NET