Distinct Combination of Two Columns

Question

I am currently trying to find distinct combinations within a table as two of the columns have a many to many relationship with each other.

The data is all around backup policies being run on against particularly clients and could be summarized as below:

The answer I would like to generate for the above table would be 7 as there are that number of distinct combinations.

Has anyone got an idea of how this might be done? I have tried experimenting with nested counts and distinct values (where I was able to filter to one column but not both).

Answer 1

You can count distinct elements by running:

select count(distinct policy_id, client_id) from policy_client;

Another option would be to group by and count that:

select count(*) from (select policy_id, client_id from policy_client group by 1,2) a;

Run both version and see which one performs better on your dataset.

A very quick way but not totally accurate if you have a key on (policy_id and client_id) you can also check the cardinality of that index but that's an approximate not exact number.

Answer 2

I think the simplest would be:

SELECT COUNT(DISTINCT policy, client) 
         AS distinct_combinations
FROM table_name ;