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)
);
CREATE INDEX st_ix ON test (status_to);

Records: 0  Duplicates: 0  Warnings: 0


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'),
(45, 'Single_1'), (46, 'Single_2');

Records: 33  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
4

13
Action
1

18
Action
2

23
Action
3

1
Draft
1

10
Draft
2

26
Draft
3

27
Draft
4

6
Let
1

15
Let
2

20
Let
3

25
Let
4

3
Publish
4

12
Publish
3

17
Publish
2

22
Publish
1

45
Single_1
1

46
Single_2
1

5
Sold
1

14
Sold
2

19
Sold
3

24
Sold
4

8
Test_val_1
1

35
Test_val_1
2

9
Test_val_2
1

31
Test_val_2
2

2
Unpublish
1

11
Unpublish
2

16
Unpublish
3

21
Unpublish
4


SELECT COUNT(rn) OVER (PARTITION BY rn) 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
WHERE rn > 1
GROUP BY status_to
ORDER BY status_to
LIMIT 1;


Unique count


2


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
HAVING MAX(rn) > 1
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


SELECT
  COUNT(DISTINCT(status_to)) AS "Unique count"
FROM
  test;


Unique count


10


SELECT
  COUNT(status_to)  AS "Unique count"
FROM
  test
WHERE status_to IS NOT NULL
GROUP BY status_to
HAVING COUNT(status_to) > 1;


Unique count


4

4

4

4

4

2

2

4


EXPLAIN ANALYZE
SELECT
  COUNT(DISTINCT(status_to)) AS "Unique count"
FROM
  test;


EXPLAIN


-> Aggregate: count(distinct test.status_to)  (cost=6.80 rows=1) (actual time=0.034..0.034 rows=1 loops=1)
    -> Covering index skip scan for deduplication on test using st_ix  (cost=3.40 rows=34) (actual time=0.012..0.031 rows=11 loops=1)



EXPLAIN ANALYZE
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, rn
ORDER BY status_to
LIMIT 1;


EXPLAIN


-> Limit: 1 row(s)  (actual time=0.290..0.290 rows=1 loops=1)
    -> Sort: tab.status_to  (actual time=0.289..0.289 rows=1 loops=1)
        -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.268..0.272 rows=30 loops=1)
            -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.267..0.267 rows=30 loops=1)
                -> Window aggregate with buffering: count(rn) OVER (PARTITION BY (tab.rn = 1) )   (actual time=0.188..0.246 rows=30 loops=1)
                    -> Sort: `(tab.rn = 1)`  (actual time=0.145..0.148 rows=30 loops=1)
                        -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.128..0.132 rows=30 loops=1)
                            -> Temporary table with deduplication  (cost=5.00..5.00 rows=0) (actual time=0.127..0.127 rows=30 loops=1)
                                -> Table scan on tab  (cost=2.50..2.50 rows=0) (actual time=0.102..0.107 rows=30 loops=1)
                                    -> Materialize  (cost=2.50..2.50 rows=0) (actual time=0.102..0.102 rows=30 loops=1)
                                        -> Sort: test.status_to, test.t_id  (actual time=0.091..0.093 rows=30 loops=1)
                                            -> Table scan on <temporary>  (cost=2.50..2.50 rows=0) (actual time=0.073..0.077 rows=30 loops=1)
                                                -> Temporary table  (cost=2.50..2.50 rows=0) (actual time=0.072..0.072 rows=30 loops=1)
                                                    -> Window aggregate: row_number() OVER (PARTITION BY test.status_to ORDER BY test.status_to )   (actual time=0.053..0.066 rows=30 loops=1)
                                                        -> Sort: test.status_to, test.status_to  (cost=6.44 rows=30) (actual time=0.050..0.052 rows=30 loops=1)
                                                            -> Filter: (test.status_to is not null)  (cost=6.44 rows=30) (actual time=0.015..0.032 rows=30 loops=1)
                                                                -> Index range scan on test using st_ix over (NULL < status_to)  (cost=6.44 rows=30) (actual time=0.012..0.026 rows=30 loops=1)
t_id	status_to	rn
4	Action	4
13	Action	1
18	Action	2
23	Action	3
1	Draft	1
10	Draft	2
26	Draft	3
27	Draft	4
6	Let	1
15	Let	2
20	Let	3
25	Let	4
3	Publish	4
12	Publish	3
17	Publish	2
22	Publish	1
45	Single_1	1
46	Single_2	1
5	Sold	1
14	Sold	2
19	Sold	3
24	Sold	4
8	Test_val_1	1
35	Test_val_1	2
9	Test_val_2	1
31	Test_val_2	2
2	Unpublish	1
11	Unpublish	2
16	Unpublish	3
21	Unpublish	4