[SOLVED] How to retrieve Price breakdown by month.

[RedMist]

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.

[RedMist]

Oh... by the way...

 

MySQL client version: 5.0.45

 

table : orders

fields : order_date | price

 

(Other fields dont matter) 

[fenway]

Add:

 

sum( IF( price BETWEEN 0 and 250 ), price, 0 ) Asum

[RedMist]

Add:

 

sum( IF( price BETWEEN 0 and 250 ), price, 0 ) Asum

 

Ooh, now this looks like it could work!!!.. but I'm getting an error..

 

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '), price, 0 ) Asum
FROM orders

 

But I can't see an error there.  Right number of brackets, etc..  Does it need an END IF somewhere?

[fenway]

Actually, wrong number of parens, my bad:

 

sum( IF( price BETWEEN 0 and 250, price, 0 ) Asum

[RedMist]

I think it's

 

sum( IF( price BETWEEN 0 and 250, price, 0 )) Asum

 

But that's perfect!  Thank you very much indeed!

 

(Mark as solved please.  I can't see how to do it myself!)

[fenway]

Sorry, tired... and the "solved" on on the bottom button-set, just above quick-reply.