Alias in Group By clause - invalid identifier [duplicate]

This question already has an answer here:

SQL - using alias in Group By

9 answers

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?

edited Nov 22 at 2:35

gfos

346316

asked Nov 20 at 22:13

Benoît Mignault

256

marked as duplicate by Community♦ Nov 20 at 23:11

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

5

alias age isn't valid for group by. Use the actual calculation instead.
– Vamsi Prabhala
Nov 20 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.
– Shaili
Nov 21 at 4:53

add a comment |

This question already has an answer here:

SQL - using alias in Group By

9 answers

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?

edited Nov 22 at 2:35

gfos

346316

asked Nov 20 at 22:13

Benoît Mignault

256

marked as duplicate by Community♦ Nov 20 at 23:11

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

5

alias age isn't valid for group by. Use the actual calculation instead.
– Vamsi Prabhala
Nov 20 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.
– Shaili
Nov 21 at 4:53

add a comment |

This question already has an answer here:

SQL - using alias in Group By

9 answers

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?

edited Nov 22 at 2:35

gfos

346316

asked Nov 20 at 22:13

Benoît Mignault

256

This question already has an answer here:

SQL - using alias in Group By

9 answers

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?

This question already has an answer here:

SQL - using alias in Group By

9 answers

sql oracle having

edited Nov 22 at 2:35

gfos

346316

asked Nov 20 at 22:13

Benoît Mignault

256

edited Nov 22 at 2:35

gfos

346316

asked Nov 20 at 22:13

Benoît Mignault

256

edited Nov 22 at 2:35

gfos

346316

edited Nov 22 at 2:35

gfos

346316

edited Nov 22 at 2:35

gfos

346316

asked Nov 20 at 22:13

Benoît Mignault

256

asked Nov 20 at 22:13

Benoît Mignault

256

asked Nov 20 at 22:13

Benoît Mignault

256

marked as duplicate by Community♦ Nov 20 at 23:11

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

marked as duplicate by Community♦ Nov 20 at 23:11

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

5

alias age isn't valid for group by. Use the actual calculation instead.
– Vamsi Prabhala
Nov 20 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.
– Shaili
Nov 21 at 4:53

add a comment |

5

alias age isn't valid for group by. Use the actual calculation instead.
– Vamsi Prabhala
Nov 20 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.
– Shaili
Nov 21 at 4:53

alias age isn't valid for group by. Use the actual calculation instead.
– Vamsi Prabhala
Nov 20 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.
– Shaili
Nov 21 at 4:53

add a comment |

2 Answers
2

active

oldest

votes

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;

edited Nov 20 at 22:24

answered Nov 20 at 22:19

gfos

346316

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 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 at 22:40

Indeed...sometime i want to use having everywhere lol Thx
– Benoît Mignault
Nov 20 at 23:11

add a comment |

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;

answered Nov 20 at 22:18

Tim

1,744620

add a comment |

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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;

edited Nov 20 at 22:24

answered Nov 20 at 22:19

gfos

346316

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 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 at 22:40

Indeed...sometime i want to use having everywhere lol Thx
– Benoît Mignault
Nov 20 at 23:11

add a comment |

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;

edited Nov 20 at 22:24

answered Nov 20 at 22:19

gfos

346316

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 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 at 22:40

Indeed...sometime i want to use having everywhere lol Thx
– Benoît Mignault
Nov 20 at 23:11

add a comment |

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;

edited Nov 20 at 22:24

answered Nov 20 at 22:19

gfos

346316

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;

edited Nov 20 at 22:24

answered Nov 20 at 22:19

gfos

346316

edited Nov 20 at 22:24

answered Nov 20 at 22:19

gfos

346316

answered Nov 20 at 22:19

gfos

346316

answered Nov 20 at 22:19

gfos

346316

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 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 at 22:40

Indeed...sometime i want to use having everywhere lol Thx
– Benoît Mignault
Nov 20 at 23:11

add a comment |

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 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 at 22:40

Indeed...sometime i want to use having everywhere lol Thx
– Benoît Mignault
Nov 20 at 23:11

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 at 22:34

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 at 22:34

@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 at 22:40

@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 at 22:40

Indeed...sometime i want to use having everywhere lol Thx
– Benoît Mignault
Nov 20 at 23:11

add a comment |

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;

answered Nov 20 at 22:18

Tim

1,744620

add a comment |

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;

answered Nov 20 at 22:18

Tim

1,744620

add a comment |

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;

answered Nov 20 at 22:18

Tim

1,744620

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;

answered Nov 20 at 22:18

Tim

1,744620

answered Nov 20 at 22:18

Tim

1,744620

answered Nov 20 at 22:18

Tim

1,744620

answered Nov 20 at 22:18

Tim

1,744620

add a comment |

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ytukyg