PostgreSQL: count unique values from multiple columns

Question

Lets have a table with two columns [col1, col2] with some values. values in col1 and col2 can be repeated. I would like to get number of unique values from both columns.

select
   count(distinct col1) as col1_unique,
   count(distinct col2) as col2_unique,
   count(distinct (col1, col2)) as total_unique
from myTable

that returns total_unique as combination of col1, col2 which is always bigger then a sum of col1_unique and col2_unique

as example: table with rows:

should return col1_unique as 2, col2_unique as 3 and total_unique 3

I can add a select for col1 and col2 and then distinct values from the select, but is there better (nicer) way to solve the task?

You could union the columns and treat them as one set, if that's what you mean. select count(distinct val) from (select col1 as val from mytable union select col2 from mytable)_; Have you got any indexes set up on these? — Zegarek
– Zegarek, Commented May 8, 2024 at 9:16
@Zegarek thank you for the idea! yes, it is about union of two columns. Yes, there is index for the table. Can I count unique values for both columns and each of the column in one request? — OcMaRUS
– OcMaRUS, Commented May 8, 2024 at 9:23

Stefanov.sm · Accepted Answer · 2024-05-08 09:25:20Z

2

A tagged union of the column values and then conditional aggregation would look and read very well. Not necessarily very efficient though.

select count(distinct col) filter (where tag = 1),
       count(distinct col) filter (where tag = 2),
       count(distinct col)
from (
  select col1 as col, 1 as tag from the_table
  union all
  select col2, 2 from the_table
) t;

Demo

answered May 8, 2024 at 9:25

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

OcMaRUS Over a year ago

much appreciate for details! Yes, it works!

Collectives™ on Stack Overflow

PostgreSQL: count unique values from multiple columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related