Search code examples
mysqlsqlgroup-bycountsubquery

SQL count rows with same value in column and group by id?

i have a table like this:

id  | value
1   |   1
2   |   1
3   |   2
4   |   3

i wonder if its possible to count the rows with same value and group them by id , but every time the code will return count 1 if i group them by id and not by value

wanted output:

id  | count
1   |   2(there are 2 rows with value 1)
2   |   2
3   |   1
4   |   1
Solution

  • You need to count the value of the column value for each id:

    select 
  t.id,
  (select count(*) from tablename where value = t.value) count
from tablename t

    See the demo
    or:

    select t.id, g.count
from tablename t inner join (
  select value, count(value) count
  from tablename 
  group by value
) g on g.value = t.value

    See the demo