CODEWITHSUNDEEP
mysqlsql
Waqar Sadiq
Waqar Sadiq

Reputation: 135

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.

Upvotes: 1

Views: 1187

Answers (2)

hjpotter92
hjpotter92

Reputation: 80639

You'd use the HAVING clause here because you want to filter on the aggregated result:

SELECT
  customer_id,
  SUM(sale_amount) AS total_amount
FROM sales_receipt
WHERE DATE(sales_receipt.sale_date) BETWEEN '2016-07-01' AND '2016-08-29'
HAVING total_amount BETWEEN 100 AND 1000
GROUP BY customer_id;

Upvotes: 2

Christian
Christian

Reputation: 827

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

Upvotes: 2

Related Questions