How to get a COUNT for both IN and NOT IN a table

Question

I have a table that contains a master list of items and an associated table that contains a unique text string only on specific values (determined by the application that inserts the data).

Example: Master Table (Assets)

Asset_ID    IP Address           Operating System    Host Name
1           123.456.789.125      Windows             BobsComputer
2           987.654.321.126      Windows             TomsComputer
3           456.123.789.127      Windows             JennysComputer

The associated table Unique_ID looks like this, joined on the Asset_ID

Special_ID  Asset_ID    Unique_ID
1           1           xhsieriyfh0308487
2           3           098uindifnoei8384

What I am trying to get is a query that gives me a count of all items both IN Unique_ID and NOT IN Unique_ID Essentially the output I'm looking for is:

With_Unique_ID      Without_Unique_ID
2                   1

I know it has something to do with making subqueries but I can't seem to find the right syntax/command order to make it work. It's postgresql if that makes a difference.

Answer 1

Something like this may be:

select count(u.asset_id) as with_unique_id,
       count(*) - count(u.asset_id) as without_unique_id
from assets a left join the_other_table u on a.asset_id = u.asset_id

(not tested)

Answer 2

You can use conditional aggregation with an outer join

select count(*) filter (where at.asset_id is not null) with_unique_id, 
       count(*) filter (where at.asset_id is null) as without_unique_id
from assets a
  left join associated_table at on at.asset_id = a.asset_id;