Page 1 of 1

Count Distinct on more than one column?

Posted: Tue Sep 27, 2016 2:09 pm
by Brett
Hey all,

I can do a count distinct on one column, but not on two columns.

Code: Select all

dbadmin=> select count(distinct date_key) from sales_fact;
 count
-------
  1826
(1 row)

dbadmin=> select count(distinct location_key) from sales_fact;
 count
-------
  1825
(1 row)

dbadmin=> select count(distinct date_key, location_key) from sales_fact;
ERROR 3457:  Function count(int, int) does not exist, or permission is denied for count(int, int)
HINT:  No function matches the given name and argument types. You may need to add explicit type casts
Does the count distinct work with more than one column?

Re: Count Distinct on more than one column?

Posted: Tue Sep 27, 2016 2:28 pm
by JimKnicely
You can try to combine the columns so that they become a single value. I assume the keys are integers, so maybe something like this?

Code: Select all

select count(distinct date_key * 10 + location_key * 100) from sales_fact;
Although, it might just be as easy to use a GROUP BY...

Code: Select all

select count(*) from (select null from test group by date_key, location_key) foo;
Or a DISTINCT like this:

Code: Select all

select count(*) from (select distinct sale_date_key, ship_date_key from test) foo;