CODEWITHSUNDEEP
sqlms-access
A Hughes
A Hughes

Reputation: 265

Multiple COUNT expressions based on different criteria in the same query

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.

  1. a Total Column (count of case IDs)
  2. Total Tasked (where Tasked = Yes)
  3. 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?

Upvotes: 1

Views: 1557

Answers (2)

Gord Thompson
Gord Thompson

Reputation: 123439

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.

Upvotes: 1

E Mett
E Mett

Reputation: 2302

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.

Upvotes: 0

Related Questions