SQL : create column that count occurences of an other column values

Question

I have a table with :

ID | name
 1 | aaa
 2 | aaa
 3 | bbb
 4 | aaa

And I would like to get the count of the column "name" in an other column :

ID | name | name_count
 1 | aaa  | 3
 2 | aaa  | 3   
 3 | bbb  | 1
 4 | aaa  | 3

I can't manage to make a good query to do such a thing. Any idea ?

Tag only the database that you use. – forpas Jul 01 '21 at 17:06 — forpas, Jul 01 '21 at 17:06

score 2 · Accepted Answer · answered Jul 01 '21 at 17:01

2

You want count(*) as a window function:

select t.*, count(*) over (partition by name) as name_count
from t;

answered Jul 01 '21 at 17:01

Gordon Linoff

How do I change this to an UPDATE query to fill a existing column? This does not work : `UPDATE t SET name_count= count(ID) over(partition by name)` – Arcyno Jul 01 '21 at 17:23
1

Managed to do it : `UPDATE t SET name_count = (SELECT count(ID) over(partition by name) FROM t)` – Arcyno Jul 01 '21 at 17:31

score 1 · Answer 2 · answered Jul 01 '21 at 17:05

1

If you need a solution that works without window functions (i.e. you still use MySQL 5.x):

select t.id, t.name, c.name_count
from t
join (
  select name, count(*) as name_count
  from t
  group by name 
) as c using (name);

answered Jul 01 '21 at 17:05

Bill Karwin

2 Answers2