Unable to sum/group a sql query...not sure why it is failing when I group by FISCAL_YEAR

Question

Simple query which should be able to be SUM/GROUPED by FISCAL_YEAR...I get an invalid column error when I go to add a group by to the outer select.

SELECT 
    (round((ESC.SUPPLE_RATE * MVAL.MAXKWH),2)) SUP_COST,
    CASE
       WHEN to_number(to_char(to_date(MVAL.cread),'MM')) > 5 
         THEN to_number((to_char(to_date(MVAL.cread),'YYYY')))+1
       else to_number(to_char(to_date(MVAL.cread),'YYYY'))
    END "FISCAL_YEAR"
from 
   ((SELECT 
        to_char(last_day(ADD_MONTHS(SYSDATE, -ROWNUM)), 'DD-MON-YYYY') MONTHS_ 
     FROM 
        DUAL 
     CONNECT BY LEVEL <= 25)
LEFT join
    (SELECT 
        to_char(READING_DATE,'DD-MON-YYYY') cread,MAX(KWH_READING) MAXKWH 
     from 
        ENERGY_METRICS_VDC 
     GROUP BY 
         to_char(READING_DATE,'DD-MON-YYYY')) MVAL on MONTHS_ = MVAL.cread
LEFT JOIN
   ENERGY_SUPPLY_CHARGE ESC on (MONTHS_ between ESC.START_DATE and ESC.END_DATE))
 order by 
    FISCAL_YEAR ASC;

Answer 1

In short, Oracle doesn't let you group by a computed field which has an alias (in this case FISCAL_YEAR). One way to solve the problem is the wrap the query inside another query, and do the grouping in the "outer" query:

SELECT * FROM (
  SELECT 
      (round((ESC.SUPPLE_RATE * MVAL.MAXKWH),2)) SUP_COST,
      CASE
         WHEN to_number(to_char(to_date(MVAL.cread),'MM')) > 5 
           THEN to_number((to_char(to_date(MVAL.cread),'YYYY')))+1
         else to_number(to_char(to_date(MVAL.cread),'YYYY'))
      END "FISCAL_YEAR"
  from 
     ((SELECT 
          to_char(last_day(ADD_MONTHS(SYSDATE, -ROWNUM)), 'DD-MON-YYYY') MONTHS_ 
       FROM 
          DUAL 
       CONNECT BY LEVEL <= 25)
  LEFT join
      (SELECT 
          to_char(READING_DATE,'DD-MON-YYYY') cread,MAX(KWH_READING) MAXKWH 
       from 
          ENERGY_METRICS_VDC 
       GROUP BY 
           to_char(READING_DATE,'DD-MON-YYYY')) MVAL on MONTHS_ = MVAL.cread
  LEFT JOIN
     ENERGY_SUPPLY_CHARGE ESC on (MONTHS_ between ESC.START_DATE and ESC.END_DATE))
  )
GROUP BY FISCAL_YEAR
order by FISCAL_YEAR ASC;

Share and enjoy.