Steven Price
Steven Price

Reputation: 91

Access SQL - Two Count() Statements in One?

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

Answers (2)

Zane Bien
Zane Bien

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

Simon Forsberg
Simon Forsberg

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

Related Questions