0

I'm trying to get top buyers by email. I'm doing this by something like:

SELECT email, MAX(dt) FROM orders GROUP BY email ORDER BY COUNT(*) DESC;

This works, I get the top repeated rows grouped by email. So top orders by email, sorted in descending order.

The part I'm now stuck with is seeing only orders that were less than a month ago.

I've tried something like the following, but I get an error:

SELECT email, MAX(dt) FROM orders WHERE TIMESTAMPDIFF(MONTH,MAX(dt),NOW()) < 1 GROUP BY email ORDER BY COUNT(*) DESC;

Error:

Invalid use of group function

I've also tried using WHERE MAX(dt) between NOW() and NOW() - INTERVAL 1 MONTH, and I get the same error.

Would anyone know how this could be done in MySQL?

The aim is only top orderers for those, who had an order in the last month.

aksu
  • 5,221
  • 5
  • 24
  • 39
Tiago
  • 1,984
  • 1
  • 21
  • 43
  • I think you want HAVING TIMESTAMPDIFF..... – user1336827 Dec 23 '13 at 17:28
  • What is the format of your dt column. I believe MySQL does not like your aggregate MAX(dt) in the WHERE clause. See this post http://stackoverflow.com/questions/3284409/use-mysql-sum-in-a-where-clause. Be careful with the HAVING. After grouping all the emails and selecting each emails max order time, mysql will then iterate all of the results, sorting by a field. This is far from efficient. – user2923779 Dec 23 '13 at 17:35
  • dt is of type datetime – Tiago Dec 23 '13 at 17:37
  • Do you want to count emails in the last month, or count all emails over time? – Barmar Dec 23 '13 at 17:38
  • Over all time, but only show those where the last order (hence MAX(dt)) was less than a month ago. Is this doable in a query? – Tiago Dec 23 '13 at 17:42

1 Answers1

2
SELECT email, MAX(dt) last_order_time
FROM orders all_orders
JOIN (SELECT DISTINCT email
      FROM orders
      WHERE TIMESTAMPDIFF(MONTH, dt, NOW()) < 1) recent
USING (email)
GROUP BY email
ORDER BY COUNT(*) DESC

or:

SELECT email, MAX(dt) last_order_time
FROM orders
GROUP BY email
HAVING TIMESTAMPDIFF(MONTH, last_order_time, NOW()) < 1
ORDER BY COUNT(*) DESC

The second version is simpler, but likely to be less efficient because it calculates the count and max for all emails before filtering out the ones with no recent orders.

Barmar
  • 741,623
  • 53
  • 500
  • 612