Waqar Sadiq Waqar Sadiq - 3 months ago 6
MySQL Question

Selecting customers who have spent more than a certain amount in a date range

I have a sales table which has one row for each sales tranaction. This table has date of sale and customer id as well.

I am looking for a way to select all those customers who have total spending in the specified range with in a specified date range. For example, get all customers who spent

between 100 and 1000
,
between 2016-07-01 and 2016-08-15
. This then has to become part of a larger query.

This query

select
customer_id,
sum(sale_amount)
from
sales_receipt
where
DATE(sales_receipt.sale_date) BETWEEN '2016-07-01' AND '2016-08-29'
group by
customer_id;


gives me all customers and their total spending in the specified date range but I need only those customers for whom
sum(sale_amount)
is
between 100 and 1000
.

Can any one help.

Answer

Try to use

        select customer_id, sum(sale_amount) from sales_receipt where
DATE(sales_receipt.sale_date) BETWEEN '2016-07-01' AND '2016-08-29' 
            group by customer_id having sum(sale_amount)>=100 and sum(sale_amount)<=100
Comments