Count distinct multiple columns in redshift

Question

I am trying to count rows which have a distinct combination of 2 columns in Amazon redshift. The query I am using is -

select count(distinct col1, col2)
from schemaname.tablename
where some filters

It is throwing me this error -

Amazon Invalid operation: function count(character varying, bigint) does not exist`

I tried casting bigint to char but it didn't work.

Answer 1

you can use sub-query and count

select count(*) from (
  select distinct col1, col2 
 from schemaname.tablename
  where some filter
) as t

Answer 2

A little late to the party but anyway: you can also try to concatenate columns using || operator. It might be inefficient so I wouldn't use it in prod code, but for ad-hoc analysis should be fine.

select count(distinct col1 || '_' || col2)
from schemaname.tablename
where some filters

Note separator choice might matter, i.e. both 'foo' || '_' || 'bar_baz' and 'foo_bar' || '_' || 'baz' yield 'foo_bar_baz' and are thus equal. In some cases this might be concern, in some it's so insignificant you can skip separator completely.

Answer 3

You can use

select col1,col2,count(*) from schemaname.tablename
where -- your filter
group by col1,col2