This has me a stummped...
If I have a this MySQL table:
UserId | Commission | Date Of Commission
1 | 200.00 | 2014-02-12
1 | 50.00 | 2014-04-01
2 | 10.00 | 2014-04-05
and I would like to display the Total Commission for a specific user per week starting from his/her first record, and display 0 for that range if there's no record.
how would I go about it?
Sample Output
UserId | Date Range | Total Commission
1 | 02/10/14 - 02/16/14 | 200.00
1 | 02/17/14 - 02/23/14 | 0.00
...
1 | 03/31/14 - 04/06/14 | 50.00
I'm not a seasoned coder so any help will be much appreciated.
Thanks!
Edit:
I have tried this:
SELECT IFNULL(SUM(Commisssion),0) Total ,DATE_SUB(`DateOfCommission`,INTERVAL 7 DAY)
AS RangStart,DATE_SUB(`DateOfCommission`,INTERVAL 1 DAY) AS RangeEnd
FROM `comms` WHERE `UserId` = '$UserID' GROUP BY DATE(`DateOfCommission`) DESC
but it starts the week with whatever date the first record was entered..