Question about using max() to find specific accounts

Databases
1

I’m trying to find members who have not made a payment for 30 days and are still on the upgraded Silver or Gold subscription. However, I’m getting an error with the following query, “Invalid use of group function”. Any clues would be appreciated!

select uID from payments where (DATE_SUB(CURDATE(),INTERVAL 30 DAY) >= max(payDate)) and uID in (select uID from users where subscription <> ‘Free’);

2

what is max(paydate) supposed to do?

after all these years of writing sql, you do realize you should have a GROUP BY clause, right?

3

Yes, sorry Rudy, I’m not sure what I was thinking. It was a long frustrating day. To answer your question, max(payDate) will give the member’s most recent payment. Here is my most recent version:

SELECT uID, MAX(date(payDate)) AS lastPayment, type, frequency FROM payments where uID in (select uID from users where subscription <> ‘Free’) GROUP BY uID;

This is working better, but not perfectly. Each row has a frequency of monthly or yearly. Each row also has a type of gold or silver. So the above query is indeed showing the most recent payment for a member, but the frequency is not always correct. For example, one user had the following payments on file:

5/5/2011 - Monthly Gold
6/5/2011 - Monthly Gold
7/14/2011 - Yearly Gold
7/14/2012 - Yearly Gold

My query is correctly showing that her most recent payment is 7/14/2012 Monthly Gold. Once I can overcome this hurdle, the next thing is to devise a way to use a dynamic having clause: having (DATE_SUB(CURDATE(),INTERVAL 40 DAY) >= lastPayment) depending if the member’s most recent payment was a monthly or yearly one. If it was monthly, then the interval of 40 days will show me members that have stopped making monthly payments and need to manually be reverted to the free membership. If the most recent payment was yearly, then the interval of 375 days will show me members that stopped making yearly payments and need to manually to manually be reverted to the free membership.

None of this would be an issue if Paypal’s IPN (payment notification program) was not so flaky.

Thanks!

4

sorry for the delay in replying

this is a common situation

it comes from mysql’s “extension” (quotes indicate sarcasm, as this one has created more harm than good) to standard sql, where it allows non-aggregate columns in the SELECT clause (in your case, type and frequency) that are “hidden” (not the best choice of word) from the GROUP BY clause

see MySQL Extensions to GROUP BY and focus on the paragraph that has the word indeterminate

you need a subquery which finds the max paydate per uid, then join this to the payments on uid and paydate, thus allowing you to pull type and frequeny from the correct row