Multiple COUNT expressions based on different criteria in the same query

Question

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.

Table 1 looks like this

Region || Case ID || Tasked || Visited

For each region I would like to show three fields.

a Total Column (count of case IDs)
Total Tasked (where Tasked = Yes)
Total Visited (where Total Visited = Yes).

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?

This would work, however, I don't want sum all true values, as some values = No — A Hughes, May 24 '14 at 12:08
@GordThompson your answer is correct! However, I benchmarked the difference in efficiency between `ABS` and `IIF` and found that `ABS` is 12 times faster. — E Mett, May 25 '14 at 07:06
@EMett I was unable to reproduce your results. See my answer below for details. — Gord Thompson, May 25 '14 at 11:19

score 1 · Answer 1 · answered May 25 '14 at 11:18

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

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

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

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

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

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

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

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.

I have opened a new question to show my test: http://stackoverflow.com/questions/23855662/which-function-is-faster-abs-or-iif — E Mett, May 25 '14 at 13:15

score 0 · Answer 2 · answered May 25 '14 at 07:06

0

Use the following:

SUM(ABS(Tasked)) AS TotalTasked

The ABS function will convert the -1 to 1

Abs is about 12 times faster than IIf! If you have thousands of records, it may make a difference.

answered May 25 '14 at 07:06

E Mett

2,272
3
18
37

Multiple COUNT expressions based on different criteria in the same query

2 Answers2

Linked