Ben Paton Ben Paton - 4 years ago 194
SQL Question

Select all records between two timestamps and then group by the number of occurrences of a value

I have a MYSQL database table called incidents. In this table I have the following columns

index | timestamp | email


I want to select the number of occurances each e-mail address appears between two timestamps.

So far I can do this:

"SELECT `email`, count(*) FROM `incidents` GROUP BY `email` ORDER BY count(*) DESC;


The above query works and gives me a list of all the unique e-mail addresses ordered by the count of how many times they appear. However it doesn't take account of the timestamp.

Ideally I want to do something like the following in one query:

SELECT `email`, `timestamp` FROM `incidents` WHERE `timestamp` BETWEEN '2015-12-01 00:00:00' AND '2015-12-30 23:59:59';

COUNT(*) GROUP BY `email` ORDER BY count(*) DESC;


This would select all the reccords between the two timestamps and then count the number of times each e-mail address appears and order by this count. How can I achieve this in one query?

Answer Source

Would there be anything wrong with simply adding a WHERE clause to your first query?

SELECT `email`, COUNT(*)
FROM `incidents`
WHERE `timestamp` BETWEEN '2015-12-01 00:00:00' AND '2015-12-30 23:59:59'
GROUP BY `email`
ORDER BY COUNT(*) DESC
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download