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

countpostgresql

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 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 if that makes a difference.

Best Answer

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)