同一查询中基于不同条件的多个 COUNT 表达式 [英] Multiple COUNT expressions based on different criteria in the same query

问题描述

我正在尝试根据 Access 中一个表中的数据创建摘要，但遇到了一些我希望有人能够解决的预期问题.

I am trying to create a summary based on data from one table in Access, but having some expected issues which I hope someone can resolve.

表 1 如下所示

Region || Case ID || Tasked || Visited

对于每个区域，我想显示三个字段.

For each region I would like to show three fields.

1. 总列(案例 ID 的计数)
2. 总任务(其中任务 = 是)
3. 总访问量(其中总访问量 = 是).

创建总计列很好，但是，一旦我开始添加 WHERE 子句 = 是的，我显然会丢失总计列中的数据.有没有办法解决这个问题?

Creating the Total Column is fine, however, once I started adding in WHERE clauses = Yes, I obviously lose data in the total column. Is there a way around this?

推荐答案

我对 E Mett 关于性能的测试结果很感兴趣，所以我试图重现它们.不幸的是，我不能.

I was intrigued by E Mett's test results regarding performance so I tried to reproduce them. Unfortunately, I could not.

我对一个包含 100 万行的表运行了测试，该表位于网络共享的后端 .accdb 文件中.我运行了三个测试(每次都重新加载前端 .accdb)并对结果求平均值.

I ran the tests against a table with 1 million rows residing in a back-end .accdb file on a network share. I ran three tests (re-loading the front-end .accdb each time) and averaged the results.

SELECT 
    COUNT(*) AS TotalRows, 
    SUM(IIf(Tasked=True,1,0)) AS TaskedRows 
FROM TestData

测试运行时间:24.8、24.0、23.8 秒
平均:24.2 秒

Test runs: 24.8, 24.0, 23.8 seconds
Average: 24.2 seconds

SELECT 
    COUNT(*) AS TotalRows, 
    SUM(Abs(Tasked)) AS TaskedRows 
FROM TestData

测试运行时间:22.3、23.8、24.9 秒
平均:23.7 秒

Test runs: 22.3, 23.8, 24.9 seconds
Average: 23.7 seconds

基于这些结果，SUM(Abs()) 可能比 SUM(IIf()) 稍微快一点，但肯定不会快 12 倍.

Based on those results SUM(Abs()) might be very slightly faster than SUM(IIf()), but certainly not 12x faster.

如果速度是一个问题，并且您有先见之明在 [Tasked] 字段上放置索引，那么真正更快的方法是

If speed is an issue and you had the foresight to put an index on the [Tasked] field, then a truly faster approach would be

SELECT 
    DCount("*", "TestData") AS TotalRows, 
    DCount("*", "TestData", "Tasked=True") AS TaskedRows

测试运行:2.1、3.5、2.3 秒
平均:2.6 秒

Test runs: 2.1, 3.5, 2.3 seconds
Average: 2.6 seconds

一如既往，查询性能调优本身就是一个有趣的游戏.

As always, query performance tuning can be an interesting game in itself.

这篇关于同一查询中基于不同条件的多个 COUNT 表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！