Perhaps you need to state what you want in English first. For example, your original query translates into the following: Consider a table reg_history_all which contains the columns id, date_status_added, and reg_status. For each id in the table find the max date_status_added excluding all id that ever have reg_status 'Deceased' but only consider records where the reg_status_added is less than 43000. Include only results where at least one record in the group has reg_status 'Registered'. Then count the number of resulting rows.