Access SQL - Two Count() Statements in One?

ms-access, sql

Solution

You can use Access's `IIF()` function to conditionally aggregate:

SELECT   handset_type,
         COUNT(IIF(handset_site_id IS NULL, 1, NULL)) AS nullcount,
         COUNT(IIF(handset_site_id = X, 1, NULL)) AS X
FROM     tbl_handsets
GROUP BY handset_type

Problem

I am creating a fairly simple Access database for work and I am stuck on this one SQL statement: I have a table that contains Handsets and the Site Ids they are assigned to. What I want to do is query the table to give me the number of `NULL` entities in one column and another column that displays the number of handsets assigned to X site id. I can get the count of the `NULL` entities very easily but to get both results in the one statement is beyond me. This is what I have so far: ``` SELECT Handset_Type, COUNT(*) as "Number of null handsets" FROM tbl_Handsets WHERE Handset_Site_Id is Null GROUP BY Handset_Type; ``` So now I have the count of `null` handsets but now I need the count of handsets assigned to X as well. Something like below should be the output: ``` HANDSET ||| NULL ||| X handset 1 ||| 50 ||| 5 handset 2 ||| 20 ||| 10 ``` Can anyone please please help?

Original source