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:

enter image description here

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).

  • Can you add your expected output and your SHOW CREATE TABLE table; statement? – oNare Aug 18 '15 at 13:00
up vote 5 down vote accepted

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.

I think the simplest would be:

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

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.