PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 2 distinct columns in select statement in Postgres

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
Tags:

2

Answers


  1. 0 
    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

    Login or Signup to reply.
  2. 0

    It’s not obvious at first glance, but the structure of your statement is

    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

    The first subquery in the from clause has an alias (totalinv), the second subquery in the from clause is missing one (where my comment point at), the rest would be a further syntax error.

    What you’ll need to do is wrap each of the three (two) top-level select statements in their own parenthesis, and add a top-level select without a from clause:

    SELECT
  (
    SELECT count(*)
    FROM (
      SELECT DISTINCT "Name", "Location", "Interactivity"
      FROM idtable
    ) totalinv
  ) AS "Total_Inventory",
  (
    SELECT count(*)
    FROM (
      SELECT DISTINCT "Name", "Location", "Interactivity"
      FROM idtable
      WHERE "Active" = 'true'
    ) totalact
  ) AS "Active_IDs",
  'Test' AS "ID Type";

    For a good way to get rid of the subqueries entirely, see the simplification in @RomanPekar’s answer.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search