CODEWITHSUNDEEP
mysqlsqldatabase
Anjum
Anjum

Reputation: 681

Show count as well as repeated rows in mysql

I want to show repeated rows as well count of its duplicate occurrence from mysql table. For example, we have a table:

user_id   course_id 
   01         33
   01         44
   02         55
   02         66
   02         77

I want to show results as per below:

    user_id   course_id   count
   01         33            02
   01         44            02 
   02         55            03
   02         66            03  
   02         77            03

This may be quite a easy query, but i am not getting it right. When i use count(user_id), it asks for group_by else query throws error. Any help would be highly appreciated.

Upvotes: 1

Views: 63

Answers (1)

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

use window function for mysql 8.0 and sql server

select *,count(user_id) over()
from table

for mysql lower version of 8

 select t1.*,( select count(user_id) 
          from table t2 where t1.user_id=t2.user_id) as cunt    
from table t1

Upvotes: 2

Related Questions