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

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

i have database this

| contact | incident | opentime | country | product | | c1 | | 1/1/2014 | mx | office | | c2 | i1 | 2/2/2014 | br | sap | | c3 | | 3/2/2014 | | sap | | c4 | i2 | 3/3/2014 | | sap | | c5 | i3 | 3/4/2014 | | office | | c6 | | 3/5/2014 | tw | sap |

i want run query criteria on country , and open time, , want receive this:

| product | contacts | incidents | | | no incidents | | | office | 1 | 1 | | sap | 2 | 2 |

i can 1 part work query like

select service, count( database criterias , incident null //(or not null) depending on row grouping product

what struggling counting incident null, , incident not null on same table result of same query in illustration above.

i have tried following

select service service, (select count contacts database incident null) contact, (select count contacts database incident not null) incident database criterias , incident null //(or not null) depending on row grouping product

the issue have above sentence whatever criteria utilize on "main" select ignored nested selects.

i have tried using union well, did not managed create work.

ultimately resolved approach: counted total contacts per product, counted numbers of incidents , added calculated field result

select service, count (contact) total, count (incident) incidents, (total - incident) contact database <criterias> grouping service

although create work, still sure there more elegant approach it. how can retrieve different counting on same column different count criteria in 1 query?

just utilize conditional aggregation:

select product, sum(iif(incident not null, 1, 1)) incidents, sum(iif(incident null, 1, 1)) noincidents database criterias grouping product;

sql vba ms-access