0

I have a mysql table with following columns

order_id customer_id total_amount date_added

Now, i would like to make a query for a particular customer to know if the customer has made more than or equal to 12000$ amount of order over six months period of his/her order history. The period is not recent.

Is there any solution for this?

Regards, Dipen

Dipen Ghale
  • 3
  • 2

1 Answers1

1
select sum(total_amount) >= 12000 over_12000
from mytable
where customer_id = nnn
and date_added between 'YYYY-MM-DD' and date_add('YYYY-MM-DD', interval 6 month)

Replace nnn with the particular customer ID, and YYYY-MM-DD with the start of the 6 month period that you're interested in.

UPDATE:

This will use the 6 month period leading up to the customer's last order:

select sum(total_amount) >= 12000 over_1200
from mytable join (select max(date_added) last_date
                   from mytable
                   where customer_id = nnn) last
where customer_id = nnn
and date_added between last_date and date_sub(last_date, interval 6 month)

UPDATE 2:

select m1.date_added end_date, sum(m2.total_amount) >= 12000 
from mytable m1 join mytable m2 on m2.date_added between date_sub(m1.date_added, interval 6 month) and m1.date_added and m1.customer_id = m2.customer_id
where m1.customer_id = nnn
group by end_date
order by end_date
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • I do not have start date. Instead i need to calculate according to the previous orders made. – Dipen Ghale Feb 07 '13 at 06:05
  • Which previous order do you start counting from? The most recent? – Barmar Feb 07 '13 at 06:19
  • Thanks Barmar. My aim is to check whether the customer has made 12000 or above amount of order in six month period of all orders. The query you made is for fixed date range. There can not be fixed date. I think we need to take reference for each of last orders and compare with date range of six month. – Dipen Ghale Feb 07 '13 at 06:56
  • See update 2, it looks at the 6 month period behind each order. – Barmar Feb 07 '13 at 07:01
  • Why not return results for all customers? – Strawberry Feb 07 '13 at 07:08
  • Because he said "particular customer", not all customers. – Barmar Feb 07 '13 at 07:11
  • I executed query but returned an empty result set (i.e. zero rows) and off course i know there is order more than 12000 for the customer i am testing in 6 month period. Can you check once your query? – Dipen Ghale Feb 07 '13 at 07:20
  • Can you make a sqlfiddle with sample data? – Barmar Feb 07 '13 at 07:27
  • Try the updated query, I got the dates backward in the BETWEEN expression. – Barmar Feb 07 '13 at 07:29
  • Thanks Barmer. The updated query worked but the sum returns 1 instead of total sum. Hopefully, i can sort this out myself. – Dipen Ghale Feb 07 '13 at 07:45
  • It returns 1 if the total is at least 12000, 0 if it's less. – Barmar Feb 07 '13 at 07:54