Postgres 14

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

create table tab(col1 text, col2 text);
insert into tab
VALUES
  ('A', 'B'),
  ('A', 'B'),
  ('A', 'B'),
  ('A', 'D'),
  ('A', 'D'),
  ('A', 'D'),
  ('C', 'F'),
  ('C', 'G'),
  ('C', 'F');
9 rows affected

with cte as (
  SELECT col1, col2, 
         count(*) as conta,
         dense_rank() over (partition by col1 order by count(*) desc) as rnk
  FROM tab 
  GROUP by col1, col2 
) 
select col1, col2, conta
from cte
where rnk = 1
order by col1, col2;


col1
col2
conta


A
B
3

A
D
3

C
F
2


with cte as (
  SELECT col1, col2, 
         count(*) as conta,
         row_number() over (partition by col1 order by count(*) desc) as rnk
  FROM tab 
  GROUP by col1, col2 
) 
select col1, col2, conta
from cte
where rnk = 1
order by col1, col2;


col1
col2
conta


A
D
3

C
F
2