Menu
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?
757
In SQL multiple fields may also be added with DISTINCT clause. DISTINCT will eliminate those rows where all the selected fields are identical.
SELECT DISTINCT FIELD1, FIELD2, FIELD3 FROM TABLE1 works if the values of all three columns are unique in the table. If, for example, you have multiple identical values for first name, but the last name and other information in the selected columns is different, the record will be included in the result set.
To select multiple columns from a table, simply separate the column names with commas! For example, this query selects two columns, name and birthdate , from the people table: SELECT name, birthdate FROM people; Sometimes, you may want to select all columns from a table.
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.
97
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.
1
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
