I have a subscription based website and store all recurring monthly payments in the table called payments. The following query will return the day of the month, total payments made as well as total dollars received.
select DATE_FORMAT(payDate, '%d') as dayofmonth, count(*) as totalPayments, sum(amount) as totalAmount from payments
where frequency = 'Monthly' group by dayofmonth limit 500;
I need some help figuring out how to modify this so I get the number of payments for a given day of the month, instead of the total for that day. For example:
04/01/2011 - 3 new payments
05/01/2011 - 3 recurring payments and no new ones
06/01/2011 - 1 new payment plus the 3 recurring payments
07/01/2011 - 4 new payments plus 4 recurring payments
So the first row returned, which is 01 for the 1st of the month, should show that I have 8 recurring payments, not a total of 19 payments.