I want to get data by financial year wise sum of bill amount I have written this query but why It is showing error like in FY: invalid identifier

Question

SELECT
   CASE WHEN MONTH(A.BILL_DT)>=7 THEN
          CONCAT(YEAR(A.BILL_DT),YEAR(A.BILL_DT)+1)
   ELSE CONCAT(YEAR(A.BILL_DT)-1, YEAR(A.BILL_DT)) END AS FY,
   SUM(A.BILL_AMT)
FROM REVADMIN.REV_BILL_HEADER A
GROUP BY FY

Why I am getting error like this FY: invalid identifier

Answer 1

You'll have to group by the whole case expression, not its alias.

  SELECT CASE
            WHEN MONTH (A.BILL_DT) >= 7
            THEN
               CONCAT (YEAR (A.BILL_DT), YEAR (A.BILL_DT) + 1)
            ELSE
               CONCAT (YEAR (A.BILL_DT) - 1, YEAR (A.BILL_DT))
         END AS FY,
         SUM (A.BILL_AMT)
    FROM REVADMIN.REV_BILL_HEADER A
GROUP BY CASE
            WHEN MONTH (A.BILL_DT) >= 7
            THEN
               CONCAT (YEAR (A.BILL_DT), YEAR (A.BILL_DT) + 1)
            ELSE
               CONCAT (YEAR (A.BILL_DT) - 1, YEAR (A.BILL_DT))
         END;

---

Note that you can't literally copy/paste "everything", using alias as it won't work either; for example:

SQL> select deptno as fy,
  2         sum(sal) sum_salary
  3  from emp
  4  group by deptno as fy;
group by deptno as fy                  --> see what is asterisk pointing to?
                *
ERROR at line 4:
ORA-00933: SQL command not properly ended


SQL> select deptno as fy,
  2         sum(sal) sum_salary
  3  from emp
  4  group by deptno;

        FY SUM_SALARY
---------- ----------
        30       9872
        20      12506
        10      10063

SQL>

Answer 2

Try this: Instead of grouping with the case statement you can use it as a sub query and sum afterwards.

select FY, SUM(BILL_AMT) as BILL_AMT from (SELECT
   CASE WHEN MONTH(A.BILL_DT)>=7 THEN
          CONCAT(YEAR(A.BILL_DT),YEAR(A.BILL_DT)+1)
   ELSE CONCAT(YEAR(A.BILL_DT)-1, YEAR(A.BILL_DT)) END AS FY,
  A.BILL_AMT 
FROM REVADMIN.REV_BILL_HEADER A)
GROUP BY FY