dreamMyDream2014
2015-12-08 17:05
浏览 147
已采纳

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!

图片转代码服务由CSDN问答提供 功能建议

您好我有一个数据库,其中包含一个包含电子邮件地址的列,第二列包含类别,第三列包含日期。

我想要做的是计算多个日期范围之间“A”类别中唯一电子邮件地址的数量。 所以我有这个:

  SELECT COUNT(DISTINCT email)作为计数器
FROM表
WHERE category =“A”AND date&lt;  “2015-12”和日期&gt;  “2015-11”; 
   
 
 

然后我将对第二个日期范围单独进行查询。

现在这里 我的问题:

如果电子邮件地址在第一个月和第二个月出现,它将继续计算两个月,因为它在我查询的范围内是唯一的。

如何创建一个查询,计算一年的唯一电子邮件地址,然后计算一个月内的不同条目,而不包括重复项?

谢谢!

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • duanbichou4942 2015-12-08 17:17
    已采纳

    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);"
    
    点赞 评论
  • dss67853 2015-12-08 18:10

    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.

    点赞 评论

相关推荐 更多相似问题