CODEWITHSUNDEEP
sqlvbams-access
Gabriel
Gabriel

Reputation: 439

How to Count the same field with different criteria on the same Query

I have a database like this

| Contact | Incident | OpenTime | Country | Product |
|    C1   |          | 1/1/2014 |   MX    |  Office |
|    C2   |    I1    | 2/2/2014 |   BR    |   SAP   |
|    C3   |          | 3/2/2014 |   US    |   SAP   |
|    C4   |    I2    | 3/3/2014 |   US    |   SAP   |
|    C5   |    I3    | 3/4/2014 |   US    |  Office |
|    C6   |          | 3/5/2014 |   TW    |   SAP   |

I want to run a query with criteria on country and and open time, and I want to receive back something like this:

| Product | Contacts with | Incidents |
|         | no Incidents  |           |
| Office  |      1        |     1     |
|   SAP   |      2        |     2     |

I can easily get one part to work with a query like

SELECT Service, count(
FROM database
WHERE criterias AND Incident is Null //(or Not Null) depending on the row
GROUP BY Product

What I am struggling to do is counting Incident is Null, and Incident is not Null on the same table as a result of the same query as in the example above.

I have tried the following

SELECT Service AS Service,
(SELECT count Contacts FROM Database Where Incident Is Null) as Contact,
(SELECT count Contacts FROM Database Where Incident Is not Null) as Incident
FROM database
WHERE criterias AND Incident is Null //(or Not Null) depending on the row
GROUP BY Product

The issue I have with the above sentence is that whatever criteria I use on the "main" select are ignored by the nested Selects.

I have tried using UNION ALL as well, but did not managed to make it work.

Ultimately I resolved it with this approach: I counted the total contacts per product, counted the numbers of incidents and added a calculated field with the result

SELECT Service, COUNT (Contact) AS Total, COUNT (Incident) as Incidents, 
(Total - Incident) as Only Contact
From Database
Where <criterias>
GROUP BY Service

Although I make it work, I am still sure that there is a more elegant approach for it. How can I retrieve the different counting on the same column with different count criteria in one query?

Upvotes: 1

Views: 2949

Answers (2)

Fionnuala
Fionnuala

Reputation: 91336

Possibly a very MS Access solution would suit:

TRANSFORM Count(tmp.Contact) AS CountOfContact
SELECT tmp.Product
FROM tmp
GROUP BY tmp.Product
PIVOT IIf(Trim([Incident] & "")="","No Incident","Incident");

This IIf(Trim([Incident] & "")="" covers all possibilities of Null string, Null and space filled.

tmp is the name of the table.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Just use conditional aggregation:

SELECT Product,
       SUM(IIF(incident is not null, 1, 1)) as incidents,
       SUM(IIF(incident is null, 1, 1)) as noincidents
FROM database
WHERE criterias 
GROUP BY Product;

Upvotes: 3

Related Questions