pb select dans requête [Résolu]

[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 :

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 :

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 :

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;

[7gyY9w1ZY6ySRgPeaefZ]

version ?