2

I tried many times by many ways but I cannot resolve this...

I'm performing an Oracle SQL query:

SELECT
    TRUNC(months_between(sysdate, DateofBirth) / 12) AS "age"
FROM players
group by age
HAVING COUNT 
    (TRUNC(months_between(sysdate, DateofBirth) / 12)) > 30;

ERROR at line 4: ORA-00904: "AGE": invalid identifier

Any ideas?

Grant Foster
  • 722
  • 2
  • 11
  • 21
Benoît Mignault
  • 55
  • 1
  • 8
  • 5
    alias `age` isn't valid for `group by`. Use the actual calculation instead. – Vamsi Prabhala Nov 20 '18 at 22:16
  • You can not use alias in same query in group by. If you want you can use it in with clause also and then result of with clause you can use in your other query. – YLG Nov 21 '18 at 04:53

2 Answers2

3

Don't put an alias in your group by:

SELECT
    TRUNC(months_between(sysdate, DateofBirth) / 12) AS "age"
FROM players
group by
    TRUNC(months_between(sysdate, DateofBirth) / 12)
HAVING
    COUNT(TRUNC(months_between(sysdate, DateofBirth) / 12)) > 30;
Tim
  • 2,756
  • 1
  • 15
  • 31
2

In Oracle and SQL Server, you cannot use a term in the GROUP BY clause that you define in the SELECT clause because the GROUP BY is executed before the SELECT clause.

https://stackoverflow.com/a/3841804/6358346

The correct way:

SELECT TRUNC(months_between(sysdate, DateofBirth) / 12) AS "age"
FROM players
GROUP BY TRUNC(months_between(sysdate, DateofBirth) / 12)
HAVING COUNT(TRUNC(months_between(sysdate, DateofBirth) / 12)) > 30;
Grant Foster
  • 722
  • 2
  • 11
  • 21
  • Thx ! Now that work but i dont have any result....lol and i supose to have something at the end of the statement..... `SQL> SELECT TRUNC(months_between(sysdate, DateofBirth) / 12) AS "age" FROM players; Result : age ---------- 36 39 38 32 34 35 37 30 33` – Benoît Mignault Nov 20 '18 at 22:34
  • 2
    @BenoîtMignault: Seems you don't want aggregation/having, but a simple `SELECT TRUNC(months_between(sysdate, DateofBirth) / 12) AS "age" FROM players WHERE TRUNC(months_between(sysdate, DateofBirth) / 12) > 30;` – dnoeth Nov 20 '18 at 22:40
  • Indeed...sometime i want to use having everywhere lol Thx – Benoît Mignault Nov 20 '18 at 23:11