SQL unique combinations

Question

I have a table with three columns with an ID, a therapeutic class, and then a generic name. A therapeutic class can be mapped to multiple generic names.

ID     therapeutic_class       generic_name
1           YG4                    insulin
1           CJ6                    maleate
1           MG9                    glargine
2           C4C                    diaoxy
2           KR3                    supplies
3           YG4                    insuilin
3           CJ6                    maleate
3           MG9                    glargine

I need to first look at the individual combinations of therapeutic class and generic name and then want to count how many patients have the same combination. I want my output to have three columns: one being the combo of generic names, the combo of therapeutic classes and the count of the number of patients with the combination like this:

Count          Combination_generic                   combination_therapeutic
2              insulin, maleate, glargine                 YG4, CJ6, MG9
1              supplies, diaoxy                           C4C, KR3

[Do not post sample data as screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557) Use formatted text - as you did at the beginning. — , Jun 28 '17 at 20:16
The data model isn't quite right. You may have separate tables for therapeutic classes and for generic names (if you don't, you should), but one more piece is missing. Presumably not every generic name can be associated with every therapeutic class; only some pairs are valid, while others are invalid. This should be in a separate, many-to-many association table, with each valid pair given a unique identifier. Then your table with patients and pairs of (therapeutic class, generic name) should instead have patients and unique id's of such valid pairs. This would make the query simpler, too. — , Jun 28 '17 at 21:02

score 3 · Answer 1 · 2017-06-28T21:31:16.580

One way to match patients by the sets of pairs (therapeutic_class, generic_name) is to create the comma-separated strings in your desired output, and to group by them and count. To do this right, you need a way to identify the pairs. See my Comment under the original question and my Comments to Gordon's Answer to understand some of the issues.

I do this identification in some preliminary work in the solution below. As I mentioned in my Comment, it would be better if the pairs and unique ID's existed already in your data model; I create them on the fly.

Important note: This assumes the comma-separated lists don't become too long. If you exceed 4000 characters (or approx. 32000 characters in Oracle 12, with certain options turned on), you CAN aggregate the strings into CLOBs, but you CAN'T GROUP BY CLOBs (in general, not just in this case), so this approach will fail. A more robust approach is to match the sets of pairs, not some aggregation of them. The solution is more complicated, I will not cover it unless it is needed in your problem.

with
         -- Begin simulated data (not part of the solution)
         test_data ( id, therapeutic_class, generic_name ) as (
           select 1, 'GY6', 'insulin'  from dual union all
           select 1, 'MH4', 'maleate'  from dual union all
           select 1, 'KJ*', 'glargine' from dual union all
           select 2, 'GY6', 'supplies' from dual union all
           select 2, 'C4C', 'diaoxy'   from dual union all
           select 3, 'GY6', 'insulin'  from dual union all
           select 3, 'MH4', 'maleate'  from dual union all
           select 3, 'KJ*', 'glargine' from dual
         ),
         -- End of simulated data (for testing purposes only).
         -- SQL query solution continues BELOW THIS LINE
     valid_pairs ( pair_id, therapeutic_class, generic_name ) as (
       select rownum, therapeutic_class, generic_name
       from   (
                select distinct therapeutic_class, generic_name
                from   test_data
              )
     ),
     first_agg ( id, tc_list, gn_list ) as (
       select t.id, 
              listagg(p.therapeutic_class, ',') within group (order by p.pair_id),
              listagg(p.generic_name     , ',') within group (order by p.pair_id)
       from   test_data t join valid_pairs p
                           on t.therapeutic_class = p.therapeutic_class
                          and t.generic_name      = p.generic_name
       group by t.id
     )
select   count(*) as cnt, tc_list, gn_list
from     first_agg
group by tc_list, gn_list
;

Output:

CNT TC_LIST            GN_LIST                      
--- ------------------ ------------------------------
  1 GY6,C4C            supplies,diaoxy               
  2 GY6,KJ*,MH4        insulin,glargine,maleate

score 2 · Answer 2 · answered Jun 28 '17 at 20:10

2

You are looking for listagg() and then another aggregation. I think:

select therapeutics, generics, count(*)
from (select id, listagg(therapeutic_class, ', ') within group (order by therapeutic_class) as therapeutics,
             listagg(generic_name, ', ') within group (order by generic_name) as generics
      from t
      group by id
     ) t
group by therapeutics, generics;

answered Jun 28 '17 at 20:10

Gordon Linoff

1,242,037
58
646
786

This may fail if two different therapeutic classes may be mapped to the same two generic names. For example both A and B may have generic names x and y. Then, with the ORDER BY clauses as written, two patients with the pairs (A, x), (B, y) and respectively (A, y), (B, x) will be put in the same group in the `GROUP BY therapeutics, generics`. – Jun 28 '17 at 20:51
Also, the OP's desired output seems to have the generic names arranged in the same order as the therapeutic classes in the comma-separated strings. That won't work with the independent ORDER BY criteria. – Jun 28 '17 at 21:00

SQL unique combinations

2 Answers2

Linked