SELECT DISTINCT每个月内的计数条目。 MYSQL

Question

Hi I have a database with a column containing email addresses and a second column containing category and a third column containing date.

What I want to do is count the number of unique email addresses in category 'A' between multiple date ranges. So I have this:

SELECT COUNT(DISTINCT email) as counter 
FROM table 
WHERE category = "A" AND date < "2015-12" AND date > "2015-11";

Then I'll do a separate query for the second date range.

NOW HERE'S MY PROBLEM:

If an email address appears in month one, and also in month two it will go on the count for both months because it's unique within the range I'm querying.

How do I create a query that will count the unique email addresses for a year let's say, then count the distinct entries in a month period without including the duplicates?

Thanks!

Answer 1

If you want custom date you can set the value to vars

$custom_date_begin = "2015-01-10";
$custom_date_end  = "2015-02-10";

then for vars and the month group by you can use somethings like this

 "SELECT COUNT(DISTINCT email) as counter 
 FROM table 
 WHERE category = 'A' 
 AND date >= '$custom_begin_date' AND date <='$custom_end_date'
 group by MONTH (date);"