SQL count by n-tuples

oracle

Took me more time than I'd like to admit to figure out how count the number of n-tuples occurrences via Oracle SQL.

Lets say table TABLE :

| ID         | Order number| Value        |
|:-----------|------------:|:------------:|
| 1          |        10   |     A     
| 2          |        10   |     B    
| 3          |        10   |     C     
| 4          |        10   |     D      
| 5          |        15   |     A      
| 6          |        15   |     B      
| 7          |        15   |     C      
| 8          |        15   |     D      
| 9          |        20   |     X      
| 10         |        20   |     Y      
| 11         |        20   |     Z      
| 12         |        30   |     M     
| 13         |        30   |     P      
| 14         |        40   |     X      
| 15         |        40   |     Y      
| 16         |        40   |     Z

I would like to count the number of order that share the same "Value" n-tuple occurrences. Output format is irrelevant as long as I can get the data, something like :

(A,B,C,D) = *2 // [Since Both order 10 and 20 share the same values]

(X,Y,Z) = *2

(M,P) = 1

n-tuple order is irrelevant (A,B,C,D) or (D,A,C,B) means the same.

Any tip or hint greatly appreciated.

Best Answer

You can use LISTAGG() function to gather the groups/tuples, then another GROUP BY to count occurrences of every group/tuple.

Assuming that (order_number, value) is unique, i.e. that no two rows have the same order_number and value:

WITH groups AS
  ( SELECT LISTAGG(value, '-') WITHIN GROUP (ORDER BY value) AS value_group
    FROM employees
    GROUP BY order_number
  )
SELECT value_group,
       COUNT(*) AS occurrences
FROM groups
GROUP BY value_group ;

You could also get the order numbers that belong to each group:

WITH groups AS
  ( SELECT LISTAGG(value, '-') WITHIN GROUP (ORDER BY value) 
             AS value_group,
           order_number
    FROM employees
    GROUP BY order_number
  )
SELECT value_group,
       COUNT(*) AS occurrences,
       LISTAGG(order_number, ', ') WITHIN GROUP (ORDER BY order_number) 
         AS order_numbers
FROM groups
GROUP BY value_group ;
Related Question