计算不同的查询MS Access [英] Count distinct query MS Access

问题描述

似乎我们不能在MS Access中使用Count (Distinct column)函数.我有以下数据和预期结果，如下所示:

It seems that we can not use Count (Distinct column) function in MS Access. I have following data and expected result as shown below

正在寻找可以提供所需结果的MS Access查询.

Looking for MS Access query which can give required result.

数据

ID     Name     Category    Person  Office
1      FIL       Global     Ben     london
1      FIL       Global     Ben     london
1      FIL       Overall    Ben     Americas
106   Asset      Global     Ben     london
156   ICICI      Overall    Rimmer  london
156   ICICI      Overall    Rimmer  london
188   UBS       Overall     Rimmer  london
9    Fund      Global       Rimmer  london

预期结果

Person  Global_Cnt  Overall_Cnt    
Ben          2         1
Rimmer       1         2

推荐答案

使用子查询从表中选择不同的值.

Use a subquery to select the distinct values from your table.

在父查询中，为GROUP BY Person，并为每个类别使用单独的Count()表达式. Count()仅计算非Null值，因此请使用IIf()为感兴趣的类别返回1，否则返回Null.

In the parent query, GROUP BY Person, and use separate Count() expressions for each category. Count() only counts non-Null values, so use IIf() to return 1 for the category of interest and Null otherwise.

SELECT
    sub.Person,
    Count(IIf(Category = 'Global', 1, Null)) AS Global_Cnt,
    Count(IIf(Category = 'Overall', 1, Null)) AS Overall_Cnt
FROM
    (
        SELECT DISTINCT ID, Category, Person
        FROM YourTable
    ) AS sub
GROUP BY sub.Person;

我不确定哪个字段标识您的唯一值，因此选择了ID，Category和Person.查询的结果集与您要求的相匹配；如果SELECT DISTINCT字段列表与您的实际数据不符，请进行更改.

I was unsure which fields identify your unique values, so chose ID, Category, and Person. The result set from the query matches what you asked for; change the SELECT DISTINCT field list if it doesn't fit with your actual data.

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