Count how many rows have the same value in msql database

Question

I'm trying to update a field nr in the database:

I have: Example data…

table ring

ID     ri             mr        nr
1                      a         0
2      a               b         0
3      a               c         0
4                      d         0
5      b               e         0
6      e               f         0

I need a query that gives this result to sum where sum count how many times value from mr apear in ri and update nr whit this value

Result shoud be like this

ID     ri             mr        nr
1                      a         2
2      a               b         1
3      a               c         0
4                      d         0
5      b               e         1
6      e               f         0

Please explain the logic behind the expected `nr` column in your output. — Tim Biegeleisen, Mar 10 '19 at 15:25
nr exist and is = with how many times mr apears in ri for each ID like for id 1 mr=a is in ri 2 times so for ID=1 nr =2. And i need to update nr with this value 2 — Util99, Mar 10 '19 at 16:30

score 1 · Answer 1 · answered Mar 10 '19 at 15:26

1

You can use a join and group by:

select t.*, coalesce(cnt, 0) as nr
from t left join
     (select mr, count(*) as cnt
      from t
      group by mr
     ) tt
     on t.mr = tt.mr;

answered Mar 10 '19 at 15:26

Gordon Linoff

1,242,037
58
646
786

forpas · Answer 2 · 2019-03-10T16:48:26.320

1

You can use a query to count the number of times that ri matches mr

select 
  t.*, 
  (select count(*) from tablename where ri = t.mr) nr
from tablename t

If the column nr exists and you want to update it:

update tablename t
set t.nr = (select count(*) from tablename where ri = t.mr)

If you use Mysql then this will do:

update tablename t
inner join (
  select mr, (select count(*) from tablename where ri = t.mr) counter from tablename t
) tt on tt.mr = t.mr
set t.nr = tt.counter

See the demo

edited Mar 10 '19 at 16:48

answered Mar 10 '19 at 15:29

forpas

160,666
10
38
76

Which dbms are you using? – forpas Mar 10 '19 at 16:46

score 0 · Answer 3 · answered Mar 10 '19 at 15:33

0

For update use

update tablename set nr = (select count(*) from tablename where ri = mr)

answered Mar 10 '19 at 15:33

Maddy Blacklisted

1,190
1
7
17

score 0 · Accepted Answer · answered Mar 10 '19 at 16:48

0

UPDATE ring a, (SELECT ri,COUNT(*) cnt FROM ring GROUP BY ri) b

SET a.nr = b.cnt WHERE mr = b.ri");

this work for me thanks

answered Mar 10 '19 at 16:48

Util99

33
8

Count how many rows have the same value in msql database

4 Answers4