Forecasting payments for the rest of the month

Databases
1

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.

Thanks!

2

How about adding a condition to the WHERE statement checking for the given month?

3

But I need all months taken into consideration, not just the given month.

4

I don’t understand. If you want to know the number of recurring payments on a certain day of a certain month, isn’t it enough to take the number of payments on that day of that month?

In your example data, the number of payments made on the 1st of July is 8, and that is the number you want.

Or am I missing something?

5

The problem is that I’m using this as a forecasting tool to determine how much money will likely come in by the end of the month. In the example months that I gave in my original post the number of recurring payments is actually eight. However, my query is adding up all payments and is returning 18. As you can see, 18 is not really the number of recurring payments. It’s simply the total of all payments I have ever received on the 1st, and that does not help me pinpoint how much revenue will be coming in for the days remaining in the month.