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.