在Access 2007 SQL中按聚合功能对分组中的差异进行计数 [英] Count Distinct in a Group By aggregate function in Access 2007 SQL

问题描述

您好，我已经浏览了一段时间论坛，并在这里问了我的第一个问题.我有点束手无策，想知道是否能得到一些帮助.我正在使用Access 2007，但尚未在网上找到一个很好的答案.

Hello I have browsed the forum for a while and am asking my first question here. I'm in a bit of a bind and was wondering if I could get some help out. I am using Access 2007 and have not found a good answer to the question on the Net yet.

我的数据是诊断代码和客户ID，而我正在寻找的原因就是为什么要为每个诊断代码找到不同的客户ID计数.理想情况下，在非Access SQL中，它看起来应该像这样:

My data is Diagnostic Codes and CustomerID's and what I am looking for is a why to find the distinct count of CustomerID's for each Diagnostic Code. Ideally in non-Access SQL it would look like this:

SELECT DiagCode, Count(Distinct(CustomerID))
FROM CustomerTable
Group By DiagCode;

我知道这是一个非常简单的问题，但是我发现的答案太复杂(多个聚合函数)或太简单.这是我为解决此问题而采取的一种方法，但这会返回太多结果:

I know this is a pretty straightforward question but the answers that I'm finding are either too complicated(multiple aggregate functions) or too simple. Here is an approach I made to solving it but this is returning too many results:

SELECT DiagCode, Count(CustomerID)
FROM CustomerTable
WHERE CustomerID in (SELECT Distinct CustomerID from CustomerTable)
Group By DiagCode;

希望我在这里很清楚，就像我说的第一篇文章，对我们的帮助表示感谢.

Hope I'm being clear here like I said my first post and any help is appreciated.

推荐答案

我不是MS Access方面的专家，上次我为它编写任何内容都花了很长时间，但这也许行得通:

I'm not expert in MS Access and it is quite a long time last time I have written anything for it, but this maybe will work:

SELECT cd.DiagCode, Count(cd.CustomerID)
FROM (select distinct DiagCode, CustomerID from CustomerTable) as cd 
Group By cd.DiagCode;

这篇关于在Access 2007 SQL中按聚合功能对分组中的差异进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！