needs_upgrade Posted March 2, 2010 Share Posted March 2, 2010 Hello guys. I want to show the total sale value of each product for the past six months. But i have to separate each monthly sale with columns. For example our current month is march, 2010. So on the 1st column, i have to show the total sale for october 2009, nov '09 on the 2nd column until march 2010 on the 6th column. What i have in mind is to use this mysql command: SELECT SUM(total) FROM sales WHERE sale_date BETWEEN DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 6 MONTH) AND DATE_SUB(LAST_DAY(CURDATE()), INTERVAL 5 MONTH) My concern is that will it fetch all sales from Oct 1, 2009 to Oct 31, 2009? If i change code between 5 month and 4 month interval, would it fetch all sales from Nov 1, 2009 to Nov 30, 2009? Please correct my code if you have better solutions. thanks so much guys Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted March 2, 2010 Share Posted March 2, 2010 Use the WHERE clause to get all the data for the date range you are interested in. Use a GROUP BY clause to form groups for each year_month within that range. I would use the mysql EXTRACT() function to get the year_month part of the values you will need in the query - http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_extract Quote Link to comment Share on other sites More sharing options...
needs_upgrade Posted March 10, 2010 Author Share Posted March 10, 2010 Please forgive me but would you be kind to show me how you would do it. just a little example. thanks so much Quote Link to comment 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.