查询计数不同值的数量？ [英] query to count number of different values?

问题描述

我在MS Access 2003中有一个表格，如下所示：

I have a table in MS Access 2003 that looks like this:

    *url            id*
    example.com    red
    example.com    blue
    example.com    blue
    other.com      red
    other.com      orange
    more.com       blue

对于每个URL，我想知道有多少唯一的ID。所以在这种情况下结果应该是：

For each URL I want to know how many unique id there are. so in this case the result should be:

    *url             count of distinct id*
    example.com          2           (because red and blue are the only values)
    other.com            2
    more.com             1

这非常类似于 SQL查询以计算不同值的数量，除了在这种情况下的解决方案不工作，因为它依赖 COUNT DISTINCT ，但在Access中不支持。我试图在Access中查找替代方法做计数不同，但我恐怕我不明白答案。

This is very similar to SQL query to count number of different values except the solution in that case does not work because it relies on COUNT DISTINCT, but that is not supported in Access. I have tried to look up alternative ways of doing count distinct in Access but I'm afraid I don't understand the answers.

所以我想这个问题可以总结作为如何模拟msaccess中的计数不同。

So I guess this problem can be summarized as "how to simulate count distinct in msaccess".

如果有人可以给我一个提示，我会非常感谢。

I would greatly appreciate it if someone can give me a hint.

推荐答案

这应该在MS Access 2003中工作（我刚刚测试它）：

This should work in MS Access 2003 (I just tested it):

SELECT url, count(id) as CountOfId
FROM
(
   SELECT distinct id, url
   FROM yourtable
) x
GROUP BY url

在这里你可以在子查询中得到不同的id和url，计算结果。

In this you get the distinct id and url in a subquery and then you count that result.

这篇关于查询计数不同值的数量？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！