pkedpker Posted November 28, 2012 Share Posted November 28, 2012 (edited) How do I select specific rows from DATETIME based on DATETIME in betweens lets say Today is 2012-11-27 I would like to get a chart based on SUM(reward) for each day for a whole week in the past. By seeing the image below it should show the same amount for each day. I was thinking something like this but it seems to merge certain days together. SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND date_credited >= (NOW() - INTERVAL 1 DAY) AND date_credited < (NOW() + INTERVAL 1 DAY) UNION ALL SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND date_credited >= (NOW() - INTERVAL 2 DAY) AND date_credited < (NOW() + INTERVAL 2 DAY) UNION ALL SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND date_credited >= (NOW() - INTERVAL 3 DAY) AND date_credited < (NOW() + INTERVAL 3 DAY) UNION ALL SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND date_credited >= (NOW() - INTERVAL 4 DAY) AND date_credited < (NOW() + INTERVAL 4 DAY) UNION ALL SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND date_credited >= (NOW() - INTERVAL 5 DAY) AND date_credited < (NOW() + INTERVAL 5 DAY) UNION ALL SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND date_credited >= (NOW() - INTERVAL 6 DAY) AND date_credited < (NOW() + INTERVAL 6 DAY) UNION ALL SELECT SUM(reward) FROM referrers WHERE referrer = '{$_SESSION['username']}' AND date_credited >= (NOW() - INTERVAL 7 DAY) AND date_credited < (NOW() + INTERVAL 7 DAY) Output is Day Reward 1 0.43 2 0.84 3 1.17 4 1.38 5 1.60 6 2.03 7 2.58 Edited November 28, 2012 by pkedpker Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted November 28, 2012 Share Posted November 28, 2012 You could try something along the lines of SELECT EXTRACT(DAY FROM date_created) as day_of_month, referrer, sum(reward) FROM referrers WHERE date_created BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW() GROUP BY day_of_month don't know that it will work or not though Quote Link to comment Share on other sites More sharing options...
Barand Posted November 28, 2012 Share Posted November 28, 2012 Use MySQL DATE() function to extract just the date element of the datetime field SELECT DATE(date_credited) as date_credited, SUM(reward) as total_reward FROM referrers WHERE referrer = '{$_SESSION['username']}' AND DATE(date_credited) BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE() GROUP BY DATE(date_credited) Quote Link to comment Share on other sites More sharing options...
pkedpker Posted November 30, 2012 Author Share Posted November 30, 2012 You could try something along the lines of SELECT EXTRACT(DAY FROM date_created) as day_of_month, referrer, sum(reward) FROM referrers WHERE date_created BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW() GROUP BY day_of_month don't know that it will work or not though Very nice thank you works perfect, except has to be date_credited not created but no problem. Use MySQL DATE() function to extract just the date element of the datetime field SELECT DATE(date_credited) as date_credited, SUM(reward) as total_reward FROM referrers WHERE referrer = '{$_SESSION['username']}' AND DATE(date_credited) BETWEEN CURDATE() - INTERVAL 6 DAY AND CURDATE() GROUP BY DATE(date_credited) Works good too, learned how to use date function thanks 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.