Group by sur un alias

Bonjour,

J'essaie de faire un group by sur un alias. D'après ce que j'ai compris, il faut faire une sous-requête. Pourtant, cela ne fonctionne pas:

Code:

---

1 2 3 4 5 6 7 8 9 10

select count(datetime), myYear from ( select datetime, (select EXTRACT(year FROM datetime) from session) as "myYear" from session ) as inline group by myYear;

---

J'ai l'erreur:

"SQL command not properly ended"

Qqn à une idée?
Merci.

Ca marche mais pas de As pour la table et des guillemets par tout

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

With v_session as ( Select sysdate as datetime from dual ) SELECT count(datetime), "myYear" FROM ( SELECT datetime, (SELECT EXTRACT(year FROM datetime) FROM v_session) AS "myYear" FROM v_session ) inline GROUP BY "myYear";

---

Merci pour l'aide. J'ai maintenant le code suivant:

Code:

---

1 2 3 4 5 6 7 8 9 10

select count(datetime), "myYear" from ( select datetime, (select EXTRACT(year FROM datetime) from session) as "myYear" from session ) inline group by "myYear";

---

Avec cette fois l'erreur:

"ORA-01427: single-row subquery returns more than one row"

J'ai lu dans la doc, qui fallait utilisé ALL/ANY/etc. dans la requête extérieur, mais cela ne fonctionne pas non plus.

Une idée?
Merci.

Les requêtes scalaires

Code:

---

1 2 3 4 5

... SELECT datetime, (SELECT EXTRACT(year FROM datetime) FROM session) AS "myYear"...

---

ne peuvent ramener qu'au maximum une seule valeur

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 Connected as mni   SQL> SQL> WITH v_session AS ( 2 SELECT sysdate AS datetime FROM dual union all 3 SELECT sysdate - 1 AS datetime FROM dual 4 ) 5 SELECT count(datetime), "myYear" 6 FROM ( 7 SELECT 8 datetime, 9 EXTRACT(year FROM datetime) AS "myYear" 10 FROM 11 v_session 12 ) inline 13 GROUP BY "myYear" 14 /   COUNT(DATETIME) myYear --------------- ---------- 2 2011   SQL>

---

Bonjour,
On peut aussi faire le GROUP BY directement sur l'expression :

Code:

---

1 2 3 4 5 6 7

WITH v_session(datetime) AS ( SELECT sysdate FROM dual union SELECT sysdate - 1 FROM dual ) SELECT count(datetime), EXTRACT(year FROM datetime) AS "myYear" FROM v_session GROUP BY EXTRACT(year FROM datetime)

---