MySQL 8.0

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

select version();


version()


8.0.30


CREATE TABLE test
(
  t_id INTEGER NOT NULL PRIMARY KEY,
  status_to VARCHAR (50)
);
INSERT INTO test VALUES
(7, NULL), (8, 'Test_val_1'), (9, 'Test_val_2'), 
 (3, 'Publish'), (4,  'Action'),  (5, 'Sold'),  (6, 'Let'), (10, 'Draft'), (11, 'Unpublish'), 
(12, 'Publish'), (13, 'Action'), (14, 'Sold'), (15, 'Let'), (26, 'Draft'), (16, 'Unpublish'),
(17, 'Publish'), (18, 'Action'), (19, 'Sold'), (20, 'Let'), (27, 'Draft'), (21, 'Unpublish'),
(22, 'Publish'), (23, 'Action'), (24, 'Sold'), (25, 'Let'), (1,  'Draft'),  (2, 'Unpublish'),
(29, NULL), (30, NULL), (35, 'Test_val_1'), (31, 'Test_val_2');

Records: 31  Duplicates: 0  Warnings: 0


SELECT
  t_id, status_to, 
  ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
FROM test
WHERE status_to IS NOT NULL
ORDER BY status_to, t_id


t_id
status_to
rn


4
Action
2

13
Action
4

18
Action
1

23
Action
3

1
Draft
2

10
Draft
4

26
Draft
1

27
Draft
3

6
Let
3

15
Let
2

20
Let
1

25
Let
4

3
Publish
4

12
Publish
1

17
Publish
3

22
Publish
2

5
Sold
4

14
Sold
2

19
Sold
3

24
Sold
1

8
Test_val_1
1

35
Test_val_1
2

9
Test_val_2
2

31
Test_val_2
1

2
Unpublish
4

11
Unpublish
3

16
Unpublish
1

21
Unpublish
2


SELECT COUNT(rn) OVER (PARTITION BY rn = 1) AS "Unique count"
FROM
(
  SELECT
    t_id, status_to, 
    ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
  FROM test
  WHERE status_to IS NOT NULL
  ORDER BY status_to, t_id
) AS tab
GROUP BY status_to
ORDER BY status_to
LIMIT 1;


Unique count


7


SELECT status_to, MAX(rn)
FROM
(
  SELECT
    t_id, status_to, 
    ROW_NUMBER() OVER (PARTITION BY status_to ORDER BY status_to) AS rn
  FROM test
  WHERE status_to IS NOT NULL
  ORDER BY status_to, t_id
) AS tab
GROUP BY status_to
ORDER BY status_to;


status_to
MAX(rn)


Action
4

Draft
4

Let
4

Publish
4

Sold
4

Test_val_1
2

Test_val_2
2

Unpublish
4

t_id	status_to	rn
4	Action	2
13	Action	4
18	Action	1
23	Action	3
1	Draft	2
10	Draft	4
26	Draft	1
27	Draft	3
6	Let	3
15	Let	2
20	Let	1
25	Let	4
3	Publish	4
12	Publish	1
17	Publish	3
22	Publish	2
5	Sold	4
14	Sold	2
19	Sold	3
24	Sold	1
8	Test_val_1	1
35	Test_val_1	2
9	Test_val_2	2
31	Test_val_2	1
2	Unpublish	4
11	Unpublish	3
16	Unpublish	1
21	Unpublish	2