pb select dans requête

Bonjour,

j'ai un problème pour extraire mes données à partir de 2 tables de façon à ce que le résultat m'affiche la moyenne des montant des commandes passées par chaque client et par trimestre.

ma requête sur 1 trimestre est la suivante:

Code:

---

1 2 3 4 5

select s.customer_id as "id",c.name as "name",avg(s.total) as "1st" from sales_order s, customer c where s.customer_id=c.customer_id and s.order_date between '1-JAN-90' and '1-MAR-90' group by s.customer_id,c.name;

---

résultat :

Citation:

---

id name 1st
---------- ------------------------- ----------
001 bidule 1540
002 machin 3080
etc..

---

ce que je veux faire :

Citation:

---

id name 1st 2nd 3rd
---------- ------------------------- ---------- ------ -------
001 bidule 1540 ..... .....
002 machin 3080

---

ma requête :

Code:

---

1 2 3 4 5 6 7 8

select distinct c.name as "name", (select AVG(s.total) from sales_order s where s.customer_id=c.customer_id and s.order_date in (select to_number(to_char(s.order_date, 'Q')) as "1st" from sales_order where order_date between '1-JAN-90' and '31-MAR-90'); (select AVG(s.total) from sales_order s where s.customer_id=c.customer_id and s.order_date in (select to_number(to_char(s.order_date, 'Q')) as "2nd" from sales_order where order_date between '1-APR-90' and '30-JUN-90'); (select AVG(s.total) from sales_order s where s.customer_id=c.customer_id and s.order_date in (select to_number(to_char(s.order_date, 'Q')) as "3rd" from sales_order where order_date between '1-JUL-90' and '30-SEP-90'); (select AVG(s.total) from sales_order s where s.customer_id=c.customer_id and s.order_date in (select to_number(to_char(s.order_date, 'Q')) as "4th" from sales_order where order_date between '1-OCT-90' and '31-DEC-90'); from customer c, sales_order s where c.customer_id=s.customer_id;

---

Ma requête ne fonctionne pas, il y a un pb sur le select je suppose .

dsl je n'ai pas corrigé la requête ;)

ma requête:

Code:

---

1 2 3 4 5 6 7

select distinct c.name as "name", (select AVG(s.total) from sales_order s where s.customer_id=c.customer_id and s.order_date in (select to_number(to_char(s.order_date, 'Q')) from sales_order where order_date between '1-JAN-90' and '31-MAR-90') as "1st", (select AVG(s.total) from sales_order s where s.customer_id=c.customer_id and s.order_date in (select to_number(to_char(s.order_date, 'Q')) from sales_order where order_date between '1-APR-90' and '30-JUN-90') as "2nd", (select AVG(s.total) from sales_order s where s.customer_id=c.customer_id and s.order_date in (select to_number(to_char(s.order_date, 'Q')) from sales_order where order_date between '1-JUL-90' and '30-SEP-90') as "3rd", (select AVG(s.total) from sales_order s where s.customer_id=c.customer_id and s.order_date in (select to_number(to_char(s.order_date, 'Q')) from sales_order where order_date between '1-OCT-90' and '31-DEC-90') as "4th" from customer c, sales_order s where c.customer_id=s.customer_id;

---

:tagcode:
version ?