How to aggregate and group by in each column?

I have this table:

major minor
  A     a
  A     a
  A     b
  B     c
  B     d

I’d like to summarize major and minor columns to get this result:

major minor major_count minor_count
A       a      3          2
A       b      3          1             
B       c      2          1
B       d      2          1

Is there any way to achieve this? I tried:

select major, minor,count(major),count(minor)
from table group by 1,2

But it didn’t count each number.

Answer

You may achieve this using window functions.

SELECT DISTINCT
    major as major,
    minor as minor,
    count(major) over (partition by major) as major_count,
    count(minor) over (partition by minor) as minor_count
FROM
    my_table
ORDER BY
    major,minor;

major	minor	major_count	minor_count
A	a	3	2
A	b	3	1
B	c	2	1
B	d	2	1

---

View on DB Fiddle

Let me know if this works for you.