SQL: How to group by column without exploding aggregating value in the table-CodePudding

How to transform table_1 to table_2 without exploding aggregating values like follows:

-- table_1
 a    b
 aaa  1
 bbb  2
 aaa  3
 ccc  4
 ccc  4
 bbb  1
 ddd  4
 bbb  2
 aaa  3

-- table_2
 a    b   count
 aaa  1   1
 bbb  2   2
 aaa  3   2
 ccc  4   2
 ccc  4   2
 bbb  1   1
 ddd  4   1
 bbb  2   2
 aaa  3   2

count column is a count of column a and b and I only know how to get count value not expended like the following code:

SELECT COUNT(*)
FROM table_1
GROUP BY a, b

-- result
 a    b   count
 aaa  1   1
 bbb  2   2
 aaa  3   2
 ccc  4   2
 bbb  1   1
 ddd  4   1

Could anyone help me?
Thanks in advance.

CodePudding user response：

You want a window function:

select t1.*,
       count(*) over (partition by a, b) as count
from table_1 t1;

You can use create table as table_2 if you want to create table_2 with these counts.