Re: help about a query

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Tue, 24 Jul 2007 06:29:13 +0200
Message-ID: <46a58019$0$25352$426a74cc@news.free.fr>

"wajim" <wajim29_at_hotmail.com> a écrit dans le message de news: 1185242798.149108.62280_at_m3g2000hsh.googlegroups.com...
| Hi everybody,
| Can someone help about this statement,
|
| table: customer, table: sales_order
|
| the question is to retrieve the average amount of all the orders each
| customer has passed during a quarter of the year 1990...
|
| This is what I have tried :
|
| select c.name, AVG(s.total) "1st Q", AVG(s.total) "2nd Q",
| AVG(s.total) "3rd Q",
| AVG(s.total) "4th Q",
| from customer c, sales_order s
| where c.customer_id=s.customer_id
| AND total IN
| (select AVG(s.total) from sales_order
| where order_date BETWEEN ('01-JAN-1990' AND '31-MAR-1990'),
| ('01-APR-1990' AND '30-JUN-1990'),
| ('01-JUL-1990' AND '3-SEP-1990')
| ('01-OCT-1990' AND '31-DEC-1990');
|
| Can you help, PLZ??
|

select c.name,

   avg(decode(to_char(s.order_date,'Q'),'1',s.total) "1st Q",
   avg(decode(to_char(s.order_date,'Q'),'2',s.total) "2nd Q",
   avg(decode(to_char(s.order_date,'Q'),'3',s.total) "3rd Q",
   avg(decode(to_char(s.order_date,'Q'),'4',s.total) "4th Q"

from customer c, sales_order s
where c.customer_id=s.customer_id
  and extract(year from s.order_date)=1990 group by c.name
/

Regards
Michel Cadot Received on Mon Jul 23 2007 - 23:29:13 CDT