MS ACCESS计数/总行数，没有重复 [英] MS ACCESS count/sum number of rows, with no duplicates

问题描述

我有以下表格，我需要计算总行数，不包括任何重复记录。

I have the following table which I need to count the total number of rows without including any duplicate records.

CustomerID
test1
test1
test2
test3
test4
test4

$ b b

正如你所看到的，总行数是6，但是有两个test1和两个test4，我希望查询返回4. IOW，我想计算<$ c的唯一值$ c> CustomerID 。

我尝试过子查询，但没有为我工作。

I've tried sub query but didn't get it to work for me.

- 更新27/06/2012 -

p>

Thanks, both worked for me:

1. 选择COUNT（*）FROM（SELECT CustomerID FROM TheTable GROUP BY CustomerID）as


2. SELECT COUNT（*）
   FROM
   （
   SELECT DISTINCT CustomerID FROM TheTable
   ）AS sub;

我现在需要一个查询，只保留那些今天添加的CustomerID，然后对它们进行计数。

I now need a query to keep only those CustomerID which were added today, then count them.

例如

CustomerID DateAdded
test1 25/06/2012
test1 25/06/2012
test2 26/06/2012
test3 27/06/2012 - Today
test4 27/06/2012 - Today
test4 27/06/2012 - Today

那么它需要返回3。

我试过在下面的查询中添加两个提供的解决方案，但是无法得到任何一个工作/返回我想要的值。

I've tried adding in the following query into the two solutions provided, but couldn't get either of them to work/return the value I want.

HAVING（（[TheTable] .DateAdded）= Date（））;

HAVING (([TheTable].DateAdded)=Date());

推荐答案

dbaseman的回答中的第二个建议对我来说是正确的。这个选择也应该工作。

The second suggestion in dbaseman's answer looks correct to me. This alternative should also work.

SELECT COUNT(*)
FROM
    (
        SELECT DISTINCT CustomerID FROM TheTable
    ) AS sub;

我不知道它是否比dbaseman的 GROUP BY 。如果您决定测试这两种方法，请添加评论告诉我们您的发现。

I don't know whether it offers any advantage over dbaseman's GROUP BY. If you decide to test both approaches, please add a comment to tell us what you find.

根据您的评论，您只想评估只有今天添加的记录。因为你没有给我们足够的关于你的表的信息，我会假装它包括一个名为 date_added 的Date / Time字段。每次添加记录时，都会在该字段中存储当前日期，时间组件为午夜。在这种情况下，您可以向子查询添加 WHERE 子句，要求数据库引擎仅考虑 date_added 等于今天的日期。 Date（）函数将给你今天的日期，以午夜作为时间组件。

Based on your comment, you want to evaluate "only those records that were added today". Since you didn't give us enough information about your table, I will pretend it includes a Date/Time field named date_added. And each time you add a record you store the current date, with the time component as midnight, in that field. In that case you can add a WHERE clause to the subquery to ask the db engine to consider only those rows where date_added is equal to today's date. The Date() function will give you today's date, with midnight as the time component.

SELECT COUNT(*)
FROM
    (
        SELECT DISTINCT CustomerID FROM TheTable
        WHERE date_added = Date()
    ) AS sub;

用您的字段名替换 date_added ，这应该工作，只要我的假设是正确的。但是，如果 date_added 值包括midnite之外的任何时间组件，则必须修改 WHERE em> all 日期/时间值。

Once you replace date_added with your field name, that should work provided my assumptions were correct. However if your date_added values include any time component other than midnite, you will have to revise the WHERE clause to capture all Date/Time values from today.

WHERE date_added >= Date() AND date_added < (Date() + 1)

这篇关于MS ACCESS计数/总行数，没有重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！