SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question about using max() to find specific accounts

    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');
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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?

    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!
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,275
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sorry for the delay in replying

    Quote Originally Posted by busboy View Post
    So the above query is indeed showing the most recent payment for a member, but the frequency is not always correct.
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •