Oracle 11g Release 2

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

CREATE TABLE mytable(
   ITEM VARCHAR2(1) NOT NULL
  ,REV  VARCHAR2(1) NOT NULL
);
INSERT INTO mytable(ITEM,REV) VALUES ('a','Q');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('b','E');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('c','D');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('d','Q');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('e','E');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('f','D');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('a','Q');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('b','E');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('c','D');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('d','Q');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('e','E');
1 rows affected

INSERT INTO mytable(ITEM,REV) VALUES ('f','D');
1 rows affected

select * from mytable


ITEM
REV


a
Q

b
E

c
D

d
Q

e
E

f
D

a
Q

b
E

c
D

d
Q

e
E

f
D


SELECT
  ITEM
, REV
, COUNT(*) OVER(PARTITION BY ITEM||REV) CONCAT_COUNT
, COUNT(*) OVER(PARTITION BY ITEM, REV) COLUMN_COUNT
FROM MYTABLE


ITEM
REV
CONCAT_COUNT
COLUMN_COUNT


a
Q
2
2

a
Q
2
2

b
E
2
2

b
E
2
2

c
D
2
2

c
D
2
2

d
Q
2
2

d
Q
2
2

e
E
2
2

e
E
2
2

f
D
2
2

f
D
2
2


SELECT
  ITEM
, REV
, COUNT(distinct ITEM||REV) OVER(PARTITION BY ITEM||REV) CONCAT_COUNT
, COUNT(distinct ITEM||REV) OVER()                       CONCAT_all_COUNT
, COUNT(*) OVER(PARTITION BY ITEM, REV) COLUMN_COUNT
FROM MYTABLE


ITEM
REV
CONCAT_COUNT
CONCAT_ALL_COUNT
COLUMN_COUNT


a
Q
1
6
2

a
Q
1
6
2

b
E
1
6
2

b
E
1
6
2

c
D
1
6
2

c
D
1
6
2

d
Q
1
6
2

d
Q
1
6
2

e
E
1
6
2

e
E
1
6
2

f
D
1
6
2

f
D
1
6
2

ITEM	REV	CONCAT_COUNT	CONCAT_ALL_COUNT	COLUMN_COUNT
a	Q	1	6	2
a	Q	1	6	2
b	E	1	6	2
b	E	1	6	2
c	D	1	6	2
c	D	1	6	2
d	Q	1	6	2
d	Q	1	6	2
e	E	1	6	2
e	E	1	6	2
f	D	1	6	2
f	D	1	6	2

ITEM	REV	CONCAT_COUNT	CONCAT_ALL_COUNT	COLUMN_COUNT
a	Q	1	6	2
a	Q	1	6	2
b	E	1	6	2
b	E	1	6	2
c	D	1	6	2
c	D	1	6	2
d	Q	1	6	2
d	Q	1	6	2
e	E	1	6	2
e	E	1	6	2
f	D	1	6	2
f	D	1	6	2

ITEM	REV	CONCAT_COUNT	CONCAT_ALL_COUNT	COLUMN_COUNT
a	Q	1	6	2
a	Q	1	6	2
b	E	1	6	2
b	E	1	6	2
c	D	1	6	2
c	D	1	6	2
d	Q	1	6	2
d	Q	1	6	2
e	E	1	6	2
e	E	1	6	2
f	D	1	6	2
f	D	1	6	2