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. Quote 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) Quote 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 Quote 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? Quote 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 Quote 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!) Quote 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. Quote 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
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.