Oracle SQL Count grouped rows in table

Question

I was wonder if it is possible preferably using a select statement on PL/SQL V11 to get the following results from this table:

Area    Store   Product
10      1       A
10      1       B
11      1       E
11      1       D
10      2       C
10      2       B
10      2       A
10      3       B
10      3       A
13      1       B
13      1       A

and Return this result, so it groups by Area, and Store and looks for and area and store with the same products. So Area 10 Store 1 has products A and B so it will look at the list for other stores that only have A and B and count them. In this example it counts Area 10 store 1/Area 10 store 3/Area 13 Store 1.

Product Count of groups
AB      3
ABC     1
DE      1

Thanks in advance for the help.

Gordon Linoff · Accepted Answer · 2018-03-19 19:25:27Z

2

Yes, you can use listagg() and then another group by:

select products, count(*)
from (select listagg(product) within group (order by product) as products
      from t
      group by area, store
     ) p
group by products;

edited Mar 19, 2018 at 19:25

user5683823

answered Mar 19, 2018 at 19:18

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle SQL Count grouped rows in table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related