Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t as
     select 1  as store, 1 as item, 0 as promo_flg from dual union all
     select 2  as store, 1 as item, 1 as promo_flg from dual union all
     select 3  as store, 1 as item, 0 as promo_flg from dual union all
     select 4  as store, 1 as item, 0 as promo_flg from dual union all
     select 5  as store, 1 as item, 1 as promo_flg from dual union all
     select 6  as store, 1 as item, 1 as promo_flg from dual union all
     select 7  as store, 1 as item, 1 as promo_flg from dual union all
     select 8  as store, 1 as item, 0 as promo_flg from dual union all
     select 9  as store, 1 as item, 0 as promo_flg from dual union all
     select 10 as store, 1 as item, 0 as promo_flg from dual 
10 rows affected

select t.*,
       count(distinct store) over (partition by item) as num_stores,
       count(distinct case when promo_flg = 1 then store end) over (partition by item) as num_promo_1
from t;


STORE
ITEM
PROMO_FLG
NUM_STORES
NUM_PROMO_1


1
1
0
10
4

2
1
1
10
4

3
1
0
10
4

4
1
0
10
4

5
1
1
10
4

6
1
1
10
4

7
1
1
10
4

8
1
0
10
4

9
1
0
10
4

10
1
0
10
4


select *
from t join
     (select item, count(distinct store) as num_stores, count(distinct case when promo_flg = 1 then store end) as num_stores_promo
      from t
      group by item
     ) tt
     using (item)


ITEM
STORE
PROMO_FLG
NUM_STORES
NUM_STORES_PROMO


1
1
0
10
4

1
2
1
10
4

1
3
0
10
4

1
4
0
10
4

1
5
1
10
4

1
6
1
10
4

1
7
1
10
4

1
8
0
10
4

1
9
0
10
4

1
10
0
10
4

STORE	ITEM	PROMO_FLG	NUM_STORES	NUM_PROMO_1
1	1	0	10	4
2	1	1	10	4
3	1	0	10	4
4	1	0	10	4
5	1	1	10	4
6	1	1	10	4
7	1	1	10	4
8	1	0	10	4
9	1	0	10	4
10	1	0	10	4

ITEM	STORE	PROMO_FLG	NUM_STORES	NUM_STORES_PROMO
1	1	0	10	4
1	2	1	10	4
1	3	0	10	4
1	4	0	10	4
1	5	1	10	4
1	6	1	10	4
1	7	1	10	4
1	8	0	10	4
1	9	0	10	4
1	10	0	10	4

STORE	ITEM	PROMO_FLG	NUM_STORES	NUM_PROMO_1
1	1	0	10	4
2	1	1	10	4
3	1	0	10	4
4	1	0	10	4
5	1	1	10	4
6	1	1	10	4
7	1	1	10	4
8	1	0	10	4
9	1	0	10	4
10	1	0	10	4

ITEM	STORE	PROMO_FLG	NUM_STORES	NUM_STORES_PROMO
1	1	0	10	4
1	2	1	10	4
1	3	0	10	4
1	4	0	10	4
1	5	1	10	4
1	6	1	10	4
1	7	1	10	4
1	8	0	10	4
1	9	0	10	4
1	10	0	10	4

STORE	ITEM	PROMO_FLG	NUM_STORES	NUM_PROMO_1
1	1	0	10	4
2	1	1	10	4
3	1	0	10	4
4	1	0	10	4
5	1	1	10	4
6	1	1	10	4
7	1	1	10	4
8	1	0	10	4
9	1	0	10	4
10	1	0	10	4

ITEM	STORE	PROMO_FLG	NUM_STORES	NUM_STORES_PROMO
1	1	0	10	4
1	2	1	10	4
1	3	0	10	4
1	4	0	10	4
1	5	1	10	4
1	6	1	10	4
1	7	1	10	4
1	8	0	10	4
1	9	0	10	4
1	10	0	10	4