RedMist Posted March 2, 2009 Share Posted March 2, 2009 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. Link to comment https://forums.phpfreaks.com/topic/147545-solved-how-to-retrieve-price-breakdown-by-month/ Share on other sites More sharing options...
RedMist Posted March 2, 2009 Author Share Posted March 2, 2009 Oh... by the way... MySQL client version: 5.0.45 table : orders fields : order_date | price (Other fields dont matter) Link to comment https://forums.phpfreaks.com/topic/147545-solved-how-to-retrieve-price-breakdown-by-month/#findComment-774534 Share on other sites More sharing options...
fenway Posted March 2, 2009 Share Posted March 2, 2009 Add: sum( IF( price BETWEEN 0 and 250 ), price, 0 ) Asum Link to comment https://forums.phpfreaks.com/topic/147545-solved-how-to-retrieve-price-breakdown-by-month/#findComment-774536 Share on other sites More sharing options...
RedMist Posted March 2, 2009 Author Share Posted March 2, 2009 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? Link to comment https://forums.phpfreaks.com/topic/147545-solved-how-to-retrieve-price-breakdown-by-month/#findComment-774546 Share on other sites More sharing options...
fenway Posted March 2, 2009 Share Posted March 2, 2009 Actually, wrong number of parens, my bad: sum( IF( price BETWEEN 0 and 250, price, 0 ) Asum Link to comment https://forums.phpfreaks.com/topic/147545-solved-how-to-retrieve-price-breakdown-by-month/#findComment-774639 Share on other sites More sharing options...
RedMist Posted March 2, 2009 Author Share Posted March 2, 2009 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!) Link to comment https://forums.phpfreaks.com/topic/147545-solved-how-to-retrieve-price-breakdown-by-month/#findComment-774772 Share on other sites More sharing options...
fenway Posted March 3, 2009 Share Posted March 3, 2009 Sorry, tired... and the "solved" on on the bottom button-set, just above quick-reply. Link to comment https://forums.phpfreaks.com/topic/147545-solved-how-to-retrieve-price-breakdown-by-month/#findComment-775341 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.