Count Distinct on more than one column?

Moderator: NorbertKrupa

Post Reply
Brett
Beginner
Beginner
Posts: 49
Joined: Fri Oct 11, 2013 1:19 am

Count Distinct on more than one column?

Post by Brett » Tue Sep 27, 2016 2:09 pm

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?

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Count Distinct on more than one column?

Post by JimKnicely » Tue Sep 27, 2016 2:28 pm

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;
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “New to Vertica SQL”