SQL Count + IIF - 访问查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22990229/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Count + IIF - Access query
提问by ProtoVB
Employees of the company are divided into categories A, B and C regardless of the division they work in (Finance, HR, Sales...)
公司员工分为 A、B 和 C 类，无论他们在哪个部门工作（财务、人力资源、销售...）
How can I write a query (Access 2010) in order to retrieve the number of employees for each category and each division?
如何编写查询 (Access 2010) 以检索每个类别和每个部门的员工人数？
The final output will be an excel sheet where the company divisions will be in column A, Category A in column B, category B in column and category C in column D.
最终输出将是一个 excel 表，其中公司部门将在 A 列中，A 类在 B 列中，B 类在列中，C 类在 D 列中。
I thought an IIF()nested in a COUNT()would do the job but it actually counts the total number of employees instead of giving the breakdown by category.
我认为IIF()嵌套在 a 中COUNT()可以完成这项工作，但它实际上计算的是员工总数，而不是按类别进行细分。
Any idea?
任何的想法？
SELECT
tblAssssDB.[Division:],
COUNT( IIF( [Category] = "A", 1, 0 ) ) AS Count_A,
COUNT( IIF( [Category] = "B", 1, 0 ) ) AS Count_B,
COUNT( IIF( [ET Outcome] = "C", 1, 0 ) ) AS Count_C
FROM
tblAssssDB
GROUP BY
tblAssssDB.[Division:];
My aim is to code a single sql statement and avoid writing sub-queries in order to calculate the values for each division.
我的目标是编写单个 sql 语句并避免编写子查询以计算每个部门的值。
回答by HansUp
Countcounts every non-Null value ... so you're counting 1 for each row regardless of the [Category]value.
Count计算每个非 Null 值......因此无论[Category]值如何，您都会为每一行计数 1 。
If you want to stick with Count...
如果你想坚持Count...
Count(IIf([Category]="A",1,Null))
Otherwise switch to Sum...
否则切换到Sum...
Sum(IIf([Category]="A",1,0))
回答by Racil Hilan
Use GROUP BYinstead of IIF. Try this:
使用GROUP BY代替IIF。尝试这个：
SELECT [Division:], [Category], Count([Category]) AS Category_Count
FROM tblAssssDB
GROUP BY [Division:], [Category];
回答by Jimmy
Try this Count:
试试这个计数：
Count(IIf([Field1]="N",1))+Count(IIf([Field2]="N",1)) ...
I grouped my qry and place Expression under this Count field I created. It worked for me
我将我的 qry 分组并将 Expression 放在我创建的这个 Count 字段下。它对我有用
回答by Earl
Select count(iif(fieldname='a',1,null)) as asde
from [table name]
where .....
