How do I select distinct count over multiple columns?

Question

How to select distinct count over multiple columns?

SELECT COUNT(DISTINCT col1, col2, col3) FROM table; 

Is there a working equivalent of this in DB2?

Odie

Answer 1

There are multiple options:

select count(*) from
   (select distinct col1, col2, col3 FROM table) t

The other would be to combine the columns via a CONCAT:

select count(distinct col1 || col2 || col3) from table

The first option is the cleaner (and likely faster) one.

Fervor

The first option doesn't work in DB2 . The second one works fine, thank you. – Odie

There was a typo in the first which I fixed. – Fervor

...concatenating the columns may yield surprising results (for example, rows with (11,1,1) and (1, 1, 11)), so you at minimum need to know something about your domain, and will almost always require a separator be added. Plus the concatenation will be slow on a table of any real size. @user2329435 - what do you mean "doesn't work"? That's the proper way to structure that type of query. – Forester

select count(*) from (select distinct col1, col2, col3 FROM table) as correlationname works. – Odie

Which platform / version of Db2? My version is on LUW – Fervor

+1 for concatenation, which also works in a larger query with other summary statistitics (e.g. select count(*), count(distinct(*)), count(distinct col1 || col2 || col3) from table). – Timbering

@Forester At which size do you expect performance problems? For me, concatenation still works ok for ~ 300,000 rows, which, to me, looks like a "real size." – Timbering

Answer 2

select count(distinct col1 || '^' || col2 || '^' || col3) from table

to avoid problems during concatenation like between 1 || 11 which would be the same as 11 || 1.

Swearingen