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

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!

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);"

Information provided is not enough for writing adequate query. So I'll try to guess details.

Lets assume that we need to count number of unique emails for each month and category.

The query could be like the following:

SELECT dt, category, COUNT(*) AS cnt 
FROM (
    SELECT LEFT(`date`,7) AS dt, category, email
    FROM table 
    GROUP BY LEFT(`date`,7), category, email
) x
GROUP BY dt, category

If you have variable date ranges, then you'd better group on daily basis, and then count emails via script for each date range.