requete avec decode()

Bonjour,
je dois ecrire une requete select utilisant DECODE() qui me retourne le nombre de commandes pour chaque client et pour chaque mois de l'année 1991.

voici le debut de ma requete:

Code:

---

1 2	select distinct customer_id,decode(EXTRACT(MONTH from ORDER_DATE),1,'Jan',2,'feb',3,'mar',4,'apr',5,'may',6,'jun',7,'jul',8,'aug',9,'sept',10,'oct',11,'nov',12,'dec') from sales_order;

---

Citation:

---

CUSTOMER_ID DECO
----------- ----
104 jul
100 mar
228 may
201 oct
203 apr
204 Jan
106 jul
104 feb
226 dec
221 mar
202 aug

CUSTOMER_ID DECO
----------- ----
204 jun
206 may
202 jul
207 apr
208 mar

---

je ne parviens pas à réaliser un count(order_id) group by customer_id !

le resultat doit etre:

Citation:

---

CUSTOMER_ID JAN FEB MAR APR MAY ....etc
204 5 2 2 ..etc
206 .....

---

merci de votre aide.

Code:

---

1 2 3 4

SELECT customer_id, mois, count(*) FROM ( SELECT DISTINCT customer_id,TO_CHAR(ORDER_DATE,'MON', 'NLS_LANGUAGE=ENGLISH') mois FROM sales_order) GROUP BY customer_id, mois;

---

Pour le mettre en colonne c'est plus galère mais il y a des exemples dans le forum :)

J'aurais plus vu quelque chose comme cela :

Code:

---

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

SELECT customer_id, count(decode(to_char(order_date, 'MM'), '01', 1)) JAN, count(decode(to_char(order_date, 'MM'), '02', 1)) FEV, count(decode(to_char(order_date, 'MM'), '03', 1)) MAR, count(decode(to_char(order_date, 'MM'), '04', 1)) AVR, count(decode(to_char(order_date, 'MM'), '05', 1)) MAI, count(decode(to_char(order_date, 'MM'), '06', 1)) JUIN, count(decode(to_char(order_date, 'MM'), '07', 1)) JUIL, count(decode(to_char(order_date, 'MM'), '08', 1)) AOUT, count(decode(to_char(order_date, 'MM'), '09', 1)) SEPT, count(decode(to_char(order_date, 'MM'), '10', 1)) OCT, count(decode(to_char(order_date, 'MM'), '11', 1)) NOV, count(decode(to_char(order_date, 'MM'), '12', 1)) DEC FROM sales_order group by customer_id;

---

en effet :ccool:

pour exemple :

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 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

SQL>; 1 SELECT object_type, 2 count(decode(to_char(created, 'MM'), '01', 1)) JAN, 3 count(decode(to_char(created, 'MM'), '02', 1)) FEV, 4 count(decode(to_char(created, 'MM'), '03', 1)) MAR, 5 count(decode(to_char(created, 'MM'), '04', 1)) AVR, 6 count(decode(to_char(created, 'MM'), '05', 1)) MAI, 7 count(decode(to_char(created, 'MM'), '06', 1)) JUIN, 8 count(decode(to_char(created, 'MM'), '07', 1)) JUIL, 9 count(decode(to_char(created, 'MM'), '08', 1)) AOUT, 10 count(decode(to_char(created, 'MM'), '09', 1)) SEPT, 11 count(decode(to_char(created, 'MM'), '10', 1)) OCT, 12 count(decode(to_char(created, 'MM'), '11', 1)) NOV, 13 count(decode(to_char(created, 'MM'), '12', 1)) DEC 14 FROM all_objects 15* GROUP BY object_type SQL>/   OBJECT_TYPE JAN FEV MAR AVR MAI JUIN JUIL AOUT SEPT OCT NOV DEC ------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- CONSUMER GROUP 0 0 0 0 0 0 2 0 0 0 0 0 INDEX PARTITION 0 0 0 0 0 0 73 0 0 0 203 0 SEQUENCE 0 0 0 0 0 0 131 0 0 5 5 0 SCHEDULE 0 0 0 0 0 0 1 0 0 0 0 0 TABLE PARTITION 0 0 0 0 0 0 60 0 0 0 188 0 RULE 0 0 0 0 0 0 4 0 0 0 13 0 JAVA DATA 0 0 0 0 0 0 306 0 0 0 0 0 QUEUE 0 0 0 0 0 0 0 0 0 0 8 0 PROCEDURE 0 0 0 0 0 0 77 0 1 2 0 0 OPERATOR 0 0 0 0 0 0 57 0 0 0 0 0 WINDOW 0 0 0 0 0 0 2 0 0 0 0 0 PACKAGE 0 0 0 0 0 0 774 0 0 74 1 0 PACKAGE BODY 0 0 0 0 0 0 719 0 0 73 1 0 LIBRARY 0 0 0 0 0 0 152 0 0 0 0 0 RULE SET 0 0 0 0 0 0 15 0 2 2 15 0 PROGRAM 0 0 0 0 0 0 12 0 0 0 0 0 LOB 0 0 0 0 0 0 0 0 0 0 20 0 TYPE BODY 0 0 0 0 0 0 164 0 0 7 0 0 CONTEXT 0 0 0 0 0 0 5 0 0 0 0 0 JAVA RESOURCE 0 0 0 0 0 0 772 0 0 0 0 0 XML SCHEMA 0 0 0 0 0 0 25 0 0 0 0 0 TRIGGER 0 0 0 0 0 0 116 0 0 48 0 0 JOB CLASS 0 0 0 0 0 0 2 0 0 0 0 0 DIRECTORY 0 0 0 0 0 0 2 0 1 1 0 0 TABLE 0 0 0 0 0 0 1242 0 1 341 633 0 INDEX 0 0 0 0 0 0 1309 0 0 396 1928 0 SYNONYM 0 0 0 0 0 0 19929 0 0 87 0 0 VIEW 0 0 0 0 0 0 3526 0 0 137 47 0 FUNCTION 0 0 0 0 0 0 254 0 1 8 0 0 WINDOW GROUP 0 0 0 0 0 0 1 0 0 0 0 0 JAVA CLASS 0 0 0 0 0 0 16420 0 0 0 0 0 INDEXTYPE 0 0 0 0 0 0 10 0 0 0 0 0 CLUSTER 0 0 0 0 0 0 10 0 0 0 0 0 TYPE 0 0 0 0 0 0 1725 0 0 217 0 0 EVALUATION CONTEXT 0 0 0 0 0 0 12 0 0 1 4 0 JOB 0 0 0 0 0 0 6 0 0 0 0 0   36 rows selected.   SQL>spool off

---