ORACLE: Looking at different date ranges with one select

Instead of hard-coding an integer, you want to cross join to set of integers.

You could do something like:

SELECT ROWNUM as rn FROM agefile
WHERE ROWNUM <= 12

Then CROSS JOIN and use rn*-1 in place of your hard-coded integers:

   with cte as (     SELECT ROWNUM as rn
                      FROM agefile
                      WHERE ROWNUM <= 12
                )
    SELECT
      to_char(last_day(add_months(sysdate,rn*-1)),'YYYYMM') yrmnth,
      FLOOR(months_between(last_day(add_months(sysdate,rn*-1)),birth_date)/12),
      count(distinct id) members
    FROM agefile
    CROSS JOIN cte
    WHERE to_char(from_date, 'YYYYMM')<=to_char(last_day(add_months(sysdate,rn*-1)),'YYYYMM') 
       and to_char(thru_date, 'YYYYMM')>=to_char(last_day(add_months(sysdate,rn*-1)),'YYYYMM')
    GROUP BY to_char(last_day(add_months(sysdate,rn*-1)),'YYYYMM'),
       FLOOR(months_between(last_day(add_months(sysdate,rn*-1)),birth_date)/12)

In this case it cleans up even better if you cross apply to a list of months rather than integers, and having a pre-built numbers/tally table to join to would make it even cleaner.

Build your months back days in a with clause and cross join with the table:

with months as
(
  select last_day(add_months(sysdate, -1)) as day from dual
  union all
  select last_day(add_months(sysdate, -2)) as day from dual
  union all
  ...
)
select
  to_char(months.day,'yyyymm') yrmnth,
  floor(months_between(months.day, birth_date) / 12),
  count(distinct id) members
from months cross join agefile
where to_char(from_date, 'yyyymm') <= to_char(months.day, 'yyyymm') 
  and to_char(thru_date, 'yyyymm') >= to_char(months.day, 'yyyymm')
group by
  to_char(months.day, 'yyyymm'),
  floor(months_between(months.day, birth_date) / 12);

Your query performs a lot of redundant operations and can be cleaned up a lot:

WITH m AS(
  SELECT last_day(add_months(sysdate,level-12)) month_end FROM dual CONNECT BY level <=12
)
SELECT
  m.month_end,
  trunc(months_between(af.birth_date, .month_end)) as age_at,
  count(*) as num_mem
FROM
  age_file af
  CROSS JOIN
  m
GROUP BY
  m.month_end,
  trunc(months_between(af.birth_date, .month_end))

I couldn't quite work out what the significance of the from date and thru date was, unless it's a filter for active membership, in which case it can probably be expressed as where m.month_end between af.from_date and af.thru_date

All the huge amounts of manipulating dates and to charring in your query is unnecessary- no need to go to the extend of calculating the last day of a month if you're then immediately going to chop the day off by tocharring to month only etc.

I recommend you leave the day in the date, because it makes it more clear that the ages expressed are on the last day of the month- I think a lot of people's default assumptions when looking at a month only date would be that it refers to the start of a month

As it stands this query calculates the current month also. If you only want the prior 12 months not including the current month make it -13 in the level query. (Query produces 1 to 12, subtracting 12 means it's -12 to 0. Subtracting 13 makes it -13 to -1)