0

Considere the table:

+--------+-------+
| id     | value |
+--------+-------+
| 1      |   A   |
| 1      |   B   |
| 2      |   A   |
| 2      |   B   |
| 3      |   A   |
| 3      |   B   |
| 3      |   C   |
| 4      |   A   |
+--------+-------+

and I want to count the group of values based on the id column. The result will be like:

+--------+---------+
| count  | value   |
+--------+---------+
| 2      |   A B   |
| 1      |   A B C |
| 1      |   A     |
+--------+---------+

Note that the cardinality of column value is dynamic.

I've tried pivoting with some subqueries but I'm not sure if I'm going in the correct way.

I appreciate any help given.

Daniel Bonetti
  • 2,306
  • 2
  • 24
  • 33
  • Could you please explain your output more? What do you mean by groups? – Arijit Kanrar Jul 30 '20 at 15:05
  • I wanted to say that every set of `value` that shares the same key should appear as a group. Then, when a different key set with the same value matches a previous key should add one to the count. – Daniel Bonetti Jul 30 '20 at 18:00

1 Answers1

1
with src_data as (
    select 1 f1, 'A' f2 from dual
    union all
    select 1, 'B' from dual
    union all
    select 2, 'B' from dual
    union all
    select 2, 'A' from dual
    union all
    select 3, 'A' from dual
    union all
    select 3, 'B' from dual
    union all
    select 3, 'C' from dual
    union all
    select 4, 'A' from dual
)
select count(1) cnt, value
from (
    select f1, listagg(f2, ' ') within group(order by f2) value
    from src_data
    group by f1
)
group by value
order by cnt desc, value

fiddle

Dornaut
  • 553
  • 3
  • 7
  • That's clever! Do you know if it's possible to join the `value` column values apart from its order? I mean, I want to consider that the key `A B` equals to `B A`. I tried to order the `with` query but with no success. – Daniel Bonetti Jul 30 '20 at 17:56
  • 1
    Edited the answer a bit. Don't "within group(order by f2)" do the trick? – Dornaut Jul 30 '20 at 18:08