I'd like to analyse orders made in the last 6 months by price and numbers, broken down by price range.
I've managed to create a MySQL which breaks down the orders by numbers...
SELECT year(order_date) y, month(order_date) m, count(*) total,
sum( price BETWEEN 0 and 250 ) A,
sum( price BETWEEN 251 and 500 ) B,
sum( price BETWEEN 501 and 1000 ) C,
sum( price BETWEEN 1001 and 2500 ) D,
sum( price BETWEEN 2501 and 5000 ) E,
sum( price BETWEEN 5001 and 15000 ) F,
sum( price BETWEEN 15001 and 25000 ) G,
sum( price > 25000 ) H
FROM orders
WHERE order_date > date_sub(curdate(), INTERVAL 6 MONTH)
GROUP BY y,m
You get something like this..
y m total A B C D E F G H
2008 9 6 2 1 3 0 0 0 0 0
2008 10 13 10 1 0 2 0 0 0 0
2008 11 43 32 9 2 0 0 0 0 0
2008 12 40 35 3 1 1 0 0 0 0
2009 1 9 8 1 0 0 0 0 0 0
2009 2 8 5 3 0 0 0 0 0 0
Which is the number of orders in each price bracket. i.e in Feb 09, we had 5 orders under $250.00
My question is, how can I now return the total value of all the orders in each price bracket? i.e in Feb 09, we had 5 orders under $250.00, worth $1100
If necessary, I can use a completely different MySQL statement.