2 distinct columns in select statement in Postgres

May 23, 2024

I’m not a frequent SQL user, but lately it has become something that I’ve been using regularly and starting to enjoy it! Currently, I am using a postres database with my data. Long story short, I am looking for my query to output only 3 values in 3 columns. The first column is simply displaying a total count of distinct IDs in my database using distinct columns (to remove duplicates).

The second column is to then display all the distinct ID’s where a value is say "true"

And then the third column is simply just column that displays "test ids" to indicate the row represents test ids.

but i get the below error:

ERROR: subquery in FROM must have an alias
LINE 2: (SELECT count(*) AS "Active_IDs" from (select distin…

I’m probably missing something obvious, or not doing something correct.

This is the query I am using:

select count(*) AS "Total_Inventory" from (select distinct "Name, "Location", "Interactivity" from idtable) totalinv ,
    (SELECT count(*) AS "Active_IDs" from (select distinct "Name", "Location", "Interactivity", 
           from idtable where "Active" = 'true') totalact), 'Test' AS "ID Type"
 from npid_it

>Solution :

select
    count(distinct (name, location, interactivity)) as "Total_Inventory",
    count(distinct (name, location, interactivity)) filter (where active = True)  as "Active_IDs",
    'Test' as "ID Type"
from idtable

sql fiddle