Discussion :

[bb5477]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

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

ce que je veux faire :

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

ma requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 .

[bb5477]

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

ma requête:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

[Invité]

version ?