Reputation: 91
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?
Upvotes: 4
Views: 818
Reputation: 23125
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
Upvotes: 5
Reputation: 13331
Not sure about Access but this is what I usually do in MySQL
SELECT SUM(IF(handset_site_id IS NULL, 1, 0)) AS number_of_null_handsets
...
Upvotes: 0