Help to write a query!

[abhit6]

There is a sales table
create table sales
(
customer varchar(25),
product varchar(25),
day integer,
month integer,
year integer,
state char(2),
quantity integer
);

for each customer prod combination
we need the min quantity and the date for it
max quan and the date for it and the avg like this

CUSTOMER PRODUCT MAX_Q DATE MIN_Q DATE AVG_Q


Can anyone help me to write a query for this?

[skhanal]

First you need to use one column as date instead of three with year, month and day.

The query needs multiple group by and join back to the same to able get everything in one row. The following should work,

select mn.customer, mn.product, min_q, mn.min_date as min_date, max_q, mx.max_data as max_date, mavg_q
from
(select s1.customer, s1.product, min_q, s1.date as min_date, avg_q
from sales as s1
join
(select customer, product, min(quantity) as min_q, avg(quantity) as avg_q
from sales
group by customer, product) as min_sales
on s1.customer = min_sales.customer
and s1.product = min_sales.product
where s1.quantity = min_q) as mn
join
(select s2.customer, s2.product, max_q, s2.date as max_date
from sales as s2
join
(select customer, product, max(quantity) as max_q
from sales
group by customer, product) as max_sales
on s2.customer = max_sales.customer
and s2.product = max_sales.product
where s2.quantity = max_q) as mx
on mn.customer = mx.customer
and mn.product = mx.product

[abhit6]

this is not working