Postgres 13

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

CREATE TABLE tbl (order_column int, col1 text);
INSERT INTO tbl VALUES
  (1, 'x')
, (2, 'x')
, (3, 'x')
, (4, 'y')
, (5, 'y')
, (6, 'x')
, (7, 'x')
, (8, 'y')
;
8 rows affected

SELECT col1, count(*)
FROM  (
   SELECT col1, order_column
        , row_number() OVER (ORDER BY order_column)
        - row_number() OVER (PARTITION BY col1 ORDER BY order_column) AS grp
   FROM tbl
   ) t
GROUP  BY col1, grp
ORDER  BY min(order_column);


col1
count


x
3

y
2

x
2

y
1


-- to exclude partitions with only a single row:
SELECT col1, count(*)
FROM  (
   SELECT col1, order_column
        , row_number() OVER (ORDER BY order_column)
        - row_number() OVER (PARTITION BY col1 ORDER BY order_column) AS grp
   FROM tbl
   ) t
GROUP  BY col1, grp
HAVING count(*) > 1
ORDER  BY min(order_column);


col1
count


x
3

y
2

x
2