CODEWITHSUNDEEP
mysql
Carjun
Carjun

Reputation: 31

mysql query on case when or data between specified date

for each relationship manager display all the customer and their total orders,who ordered more than 5 times in the last week or more than 10 times in the last 14 days,there are two tables

1.orders[date,rel. manager],
2.customer[cid,cname].

I am trying like this, thanks.

select o.RelationshipManager,c.Name,count(*) total_orders
case
when o.OrderedDate >curdate() -interval 7 day then count(*)
else
o.OrderedDate >curdate() -interval 14 day then count(*)
end
from customer c
join orders o on c.customerid=o.customerid;

Upvotes: 0

Views: 53

Answers (1)

Uueerdo
Uueerdo

Reputation: 15961

SELECT o.RelationshipManager, c.Name
   , COUNT(*) total_orders
   , COUNT(CASE WHEN o.OrderedDate > curdate() - INTERVAL 7 DAY THEN 1 ELSE NULL END) AS oneWeekCount
   , COUNT(CASE WHEN o.OrderedDate > curdate() - INTERVAL 14 DAY THEN 1 ELSE NULL END) AS twoWeekCount
FROM customer AS c
JOIN orders AS o ON c.customerid=o.customerid
-- WHERE o.OrderedDate > curdate() - INTERVAL 14 DAY 
GROUP BY o.RelationshipManager, c.Name
HAVING oneWeekCount > 5 OR twoWeekCount > 10
;

COUNT only counts non-null values, the WHERE is optional but changes the results (it should reduce the number of records inspected, but makes total_orders the same as twoWeekCount); the HAVING filters the results after the aggregation/counting has been performed.

In my experience, it is very rare for an aggregate function to be appropriate inside a conditional; I'm not even 100% sure there is an appropriate scenario for such use.

Upvotes: 1

Related Questions