CODEWITHSUNDEEP
db2mainframe
Tonan
Tonan

Reputation: 147

How do I select distinct count over multiple columns?

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?

Upvotes: 11

Views: 38982

Answers (2)

Burkhard Lau
Burkhard Lau

Reputation: 51

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.

Upvotes: 3

data_henrik
data_henrik

Reputation: 17118

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.

Upvotes: 24

Related Questions