Sql query to find how many paying members exist that have paid for 12 months

Discussion in 'Troubleshooting' started by beejeebers, Apr 18, 2010.

  1. beejeebers

    beejeebers Member

    Joined:
    Sep 14, 2007
    Messages:
    68
    I am trying to figure out how to get the members of my site who are paying members and have been doing so for over 12 months.

    I tried this:

    PHP:
    SELECT *
    FROM `amember_members`
    WHERE STATUS =1
    AND added < '2009-04-17 00:00:00'
    AND DATA REGEXP 'is_active'
    LIMIT 0 , 30
    But it's including free accounts.

    help?
    beejeebers, Apr 18, 2010
    #1
  2. alexander

    alexander Administrator Staff Member

    Joined:
    Jan 8, 2003
    Messages:
    6,279
    This will return all users who signup year ago, still active, and have at least one non free payment in past year
    Code:
    select m.*, sum(p.amount) as paid from amember_members m left join amember_payments p using(member_id) where m.added < '2009-04-17 00:00:00' and m.status=1  and p.completed=1 having paid >0
    alexander, Apr 20, 2010
    #2

Share This Page