I have this table:
x
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.
Advertisement
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 |
Let me know if this works for you.